sql server reporting services: "Must declare variable" error caused by stored procedure -- sql server reporting services

"Must declare variable" error caused by stored procedure

Ravi
9/2/2004 10:21:36 PM

I guess thats because the SPs run independently.

BTW have you considered using cursors in your SP?
[quoted text, click to view]

Re: "Must declare variable" error caused by stored procedure

steve.pantazis NO[at]SPAM salesviz.com
9/3/2004 10:01:27 AM

Ravi, the stored procedure must call itself recursively as it gets the
children for each parent. For example, here's my hierarchy

OrgNum ParentOrgNum

1001 NULL
1002 1001
1003 1002
1004 1002
1005 1001
1006 1005
1007 1006
1008 1006

If I want all the children for OrgNum 1005, the sproc first runs and
gets OrgNum 1006 as a child. Then 1006 becomes the parent and the
sproc calls itself to get all the children for 1006, which are 1007
and 1008. When the sproc tries to get their children, there are no
more, and the sproc terminates. So, I end up with 1006, 1007, and
1008 as children for 1005. During each iteration of the sproc, I
insert the child OrgNum values into a table.

So, with my dsOrgs dataset, I first run the sproc, which does the
stuff above. Then I run a select statement which retrieves all the
children from the table I populated from my sproc. All that works
fine when I run everything in the data area of Reporting Services'
Report Builder.

But that's not the problem. The problem is that I get an error
stating that I have to declare a variable, @INum. That's what my
first message covers in detail, and the problem to which I'm seeking a
solution.

[quoted text, click to view]

"Must declare variable" error caused by stored procedure

Melissa
9/3/2004 2:30:29 PM

Hi Steve,
I am doing something sort of similar... I have created a
stored procedure that takes in a couple of parameters and
passes them to the database and creates a table. I then
have a query that selects the data from the table. I
created two seperate datasets one for the stored
procedure and one for the select statement and it seems
to be working. I'm not sure how you tell it what to
execute first but another report writer here is doing the
same but she had to increase the timeout to give the
stored procedure a chance to finish otherwise it was
throwing errors. Sorry I'm not much help but I'm curious
if you've tried to use the parent group within the group
that is suppose to recursively search in a parent-child
relationship? I have the same exact thing to do that you
are doing and I would love to hear any lessons learned.
Thanks!!
[quoted text, click to view]

Re: "Must declare variable" error caused by stored procedure

steve.pantazis NO[at]SPAM salesviz.com
9/3/2004 9:37:12 PM

Melissa,
Reporting Services does a fine job handling parent-child sets of
data in tables and what not...but that's assuming you have a data set
with all the data you want. What I have is an entire hierarchy, of
which, only one part I might want to retrieve in the data set for the
report (e.g. pulling back a region and their branches vs. the whole
entire organization). So, the whole problem here is about retrieving
the data and not about how Reporting Services will handle it after the
data is retrieved.
With that being said, I've already tried to break out the stored
procedure that gets all the children vs. the SQL query that retrieves
the data produced by the stored procedure into two separate data sets.
But how would Reporting Services know when the stored procedure has
finished in order to run the second query, which retrieves the data?
By arbitrarily setting a timeout? I find that method too unreliable.
I've already broken out my stored procedure and select statement into
two data sets, but that doesn't work.
So, what else have I done? I've tried to do a recursive SQL
function, but to no avail (functions can't recursive do selects of
data), a recursive stored procedure with a varying output parameter
(Reporting Services allows only 1 value per parameter for the current
release), but to no avail, setting the output of my stored procedure
to a temp table (can't seem to get that to work), but to no avail,
using a global temp table within my stored procedure (doesn't work
because the stored procedure recursively calls itself and you can only
declare the global temp table once), but to no avail, and a bunch of
other techniques in order to recursively grab all the children for the
region I select for my data set. The Yukon release of SQL Server will
solve my problem, because I will be able to execute a single
expression and retrieve the recursive data I need in a single
operation...but I need something in the meantime. (By the way, Oracle
already supports recursion with their Connect method).
So, I ended up having to pair the stored procedure with my select in
the same dataset in order to 1) generate a list of child values and 2)
retrieve that list of values AFTER they are generated. The dataset
refreshes no problem, but I get that stupid "need to declare @INum
first" error, which I can't get rid of...it's so frustrating. I wish
I could speak to one of the Reporting Services developers over the
phone and figure this out.

[quoted text, click to view]

Re: "Must declare variable" error caused by stored procedure

steve.pantazis NO[at]SPAM gmail.com
9/24/2004 7:23:49 PM

In case anyone cares, I solved this problem myself.

The problem was related to a dataset that first calls a stored
procedure to populate a table with recursive data and then runs a
select statement to retrieve a set of data filtered by the recursive
data. What was happening was that Reporting Services was erroring
because my select was trying to fire before my stored procedure
finished. I ended up gettting a "Must declare variable" error (among
other things).

The trick is to make the select statement "wait" for the stored
procedure. How do you do this, you ask? It's simple.

You have to declare a variable and then set the execution of the
stored procedure to the variable. The select statement that follows
has to wait for the variable above to get populated with a "0", which
means the stored procedure executed properly. Then the select will
fire.

Here's a sample dataset query that uses the variable wait method:

DECLARE @ResultValue INT
EXEC @ResultNum = SV_GetSubordinates @INum,'Goals',@GoalNum

SELECT *
FROM v_Goals
WHERE
GoalNum IN (
SELECT Num
FROM SVSiblings
WHERE
INum = @INum AND
Type = 'Goals' AND
UserID = USER_ID())

See the part about "EXEC @ResultNum = "? That's the key to avoiding
the "Must declare variable" error I encountered and the misfiring of
the select statement. The select part of the query waits for
@ResultNum to be populated with a "0" before getting run.

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server reporting services group.