Is this Code right

Hi,

This is my dataset for a report. the reason i am creating this table is because i want to split the result set of the store procedure rpt_Selectinvestments, so that i can display the results of the table thats InvestmentName evenly.

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

I am also creating a Internal parameter called Split which is an integer which has the following expression

select split = case when max(rowid)%2 = 1 then max(rowid)/2) + 1 else max(rowid)/2 end from #TmpResults.

but when i try to run my report i am getting an error saying that "Split doesnt have the expected parameter type.

Some one please please help me

So what can i do in order to by pass it.

Regards,

Karen

Karenros wrote:

The first time i create this table its fine but the next time i try to run this query i get an error saying that the table or object already exist is the database.

Create table #TmpResults

( rowid int IDENTITY,

PlanId int,

PlanName varchar(200),

InvestmentName varchar(500),

InvestmentType char(1),

IsPortfolioFundOnly bit,

InvestmentId int)

Declare @.PlanId int

set @.PlanId = 682

Insert Into #TmpResults

Exec ICCStatements..rpt_SelectInvestments @.PlanId

The first time you run this it is creating a table called TmpResults. The second time you run this, it tries to create a table called TmpResults, but it looks in your database and finds that there is already one there, thus the error.

|||

so what should i do.. All i am trying to do is to split resultset into half so that i can display them in 2 tables.

Can u please help me out.

|||

how can i take the results of the sproc and insert it into a table? Is it possible to do it...

|||

Have you tried using functions instead?

You can create a function that returns a table and I would think you could insert that table into another table. I typically just select from it though instead of inserting it.

|||

can u please give me an example of that. or do u mean write a custom code to do it?

Regards

Karen

|||

Code Snippet

USE [database]

GO

/****** Object: UserDefinedFunction [dbo].[func2] Script Date: 08/03/2007 10:29:33 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[func2]

(

@.StartDate varchar(100),

@.EndDate varchar(100)

)

RETURNS TABLE

AS

RETURN (SELECT SUM(TOTAL) AS TOTAL FROM MYTABLE WHERE StartDate = @.StartDate AND EndDate = @.EndDate)

Then you could put this in a stored procedure:

Code Snippet

select SUM(TOTAL) AS TOTAL from dbo.func2('07/01/2006', '07/31/2006')

As you can see, a table is returned from func2 and you can select from it.

In your case, you would want to try to use that table that is returned and insert the first half into one table and the second half into another table.

|||

Greg,

Thanks for ur answer. This is what i have done right now, I have created a functions which is a follows

ALTER Function [dbo].[Func2]

(

@.PlanId int

)

RETURNS Table

AS

Return (Select Count(*) as RowId from PlanFund Where PlanId = @.PlanId)

and this is the sproc that i am using to poplulate my report it is as follows

ALTER PROCEDURE [dbo].[rpt_SelectInvestments] (@.PlanId AS integer)

AS

-- History

-- 08/17/2004 svanpatter/JSWCO initial version created

-- 08/30/2004 svanpatter/JSWCO add

-- Select available funds

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

-- Fund.[FundName] AS InvestmentName,

CASE

WHEN

PlanFund.PlanFundDisplayName IS NULL

THEN

Fund.ShortName

ELSE PlanFund.PlanFundDisplayName

END InvestmentName,

'F' AS InvestmentType,

--EmpIncrementPct =

--CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

--END,

--PlanFund.PlanId As InvestmentID

PlanFund.IsPortfolioFundOnly,

PlanFund.FundDisplayOrder As InvestmentID

FROM

[ClientPlan]

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

INNER JOIN PlanFund ON [ClientPlan].PlanId = PlanFund.PlanId And IsPortfolioFundOnly = "0"

INNER JOIN Fund ON PlanFund.FundId = Fund.FundId

--INNER JOIN Abbrev ON Lipper.LipperID = Abbrev.LipperID

WHERE

[ClientPlan].PlanId = @.PlanId

UNION

-- Select Portfolios

SELECT

[ClientPlan].PlanId,

[ClientPlan].PlanName,

PlanPortfolio.PortfolioName AS InvestmentName,

'P' AS InvestmentType,

--EmpIncrementPct =

-- CASE

-- WHEN EmpIncrementPct IS NULL THEN '0'

-- WHEN EmpIncrementPct = 0 THEN EmpIncrementPctOther

-- ELSE CAST( CAST(EmpIncrementPct AS integer) AS varchar(50))

-- END,

NULL,

PlanPortfolio.PortfolioId As InvestmentID

FROM [ClientPlan]

INNER JOIN PlanPortfolio ON [ClientPlan].PlanId = PlanPortfolio.PlanId

--INNER JOIN PlanAllocation ON [ClientPlan].PlanId = [PlanAllocation].PlanId

WHERE

[ClientPlan].PlanId = @.PlanId

ORDER BY

InvestmentType, InvestmentID

Select RowId from dbo.Func2(@.PlanId)

As u can see at the end of the sproc i am calling the function...

and when i run this sproc i get 2 tables one which returns the each record and the other one which returns the count for the other table. like suppose if i have 24 records... Select RowId returns 24.

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

any help will be appreciated

|||

Karenros wrote:

but when i run this sproc as a dataset in the report i dont the select Rowid part in the result set. why is that?

Are you using rpt_SelectInvestments as the sproc in your report?

If so, then you need to incorporate "select RowId from dbo.Func2(@.PlanId)" into your sproc. Right now you have it as two separate select statements.

|||

ok i dont think the function i created will work... so is there a way that i can put the results of the sproc in a parameter or a variable in the report ?

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

Regards

Karen

|||

Karenros wrote:

For ex. in my sproc i am returning the @.@.RowCount, is it Possible to access this @.@.RowCount in the report?

For the first time when you call your procedure from report with all valid parameter value. It will create list of all parameter for your report. which are useed to call the procedure next time. And you can modify parameter from menu Report - > Report parameter...

If you have parameter in stored proc with output type. It will create that also as report parameter.. and you can use them on report whereever you want whenever you want.

|||

Hi its Me,

Thanks for your answer..

So in my sproc if i do

Create proc [dbo].[procname]

@.PlanId as integer,

@.Count int output

AS

Select

<whatever> i want

fromm

tablename

Union

Select statment

where PlanId = @.PlanId

and then at the end i am setting

SEt @.Count = @.@.RowCount

Return @.count.

When i run the sproc it asks me a value for @.Count...

What should i do..

Regards

Karen

|||

select blank or null for output parameter. or just pass any value.. that doesnt make any difference to your proc. as you are not using that parameter in your proc..

|||

thanks for ur answer... But how can i get value of the output parameter in the report?

Regards,

Karen

|||

In expression just write :

=Parameters!Count.Value

And you will get the value of the parameter..