"Must declare variable" error caused by stored procedure

I'm having the most difficult time trying to generate a report that
first calls a stored procedure and then retrieves the data produced
from it.
I get the error, "An error has occurred during report
processing...query execution failed for data set dsOrgs...Must declare
the variable @.INum" when I try to run the report.
The dataset below (dsOrgs) first calls a stored procedure
(SV_GetSubordinates) that populates a table with hierarchical data.
The second part (the Select statement) then retrieves the data
produced by the stored procedure. I have no problem running this set
of SQL statements in the Data view of the Reporting Services Report
Designer.
EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
SELECT v_Orgs.*
FROM v_Orgs, SVSiblings
WHERE
v_Orgs.INum = @.INum AND
v_Orgs.INum = SVSiblings.INum AND
v_Orgs.OrgNum = SVSiblings.Num
By the way, @.INum is a parameter that will be passed to the report in
a URL string eventually. But for now, I have to use both the Preview
capability of the Report Designer and the Report Manager rendering
engine to test out my report.
I have another dataset that gets the @.OrgNum parameter value from a
selection in a drop down in my report. Here is the query for that
data set...
SELECT
NULL AS OrgNum,
'-- ALL Orgs --' AS [Description]
FROM SVGroupDefs
WHERE
INum = @.INum
UNION
SELECT
OrgNum,
[Description]
FROM SVGroupDefs
WHERE
INum = @.INum
ORDER BY [Description]
As you can see, I'm using @.INum in this dataset first so I can
populate my drop down list. When the user selects an Organization
from the drop down list, the selection returns the value for the
parameter @.OrgNum, which is used in my dsOrgs dataset along with @.INum
to retrieve the hierarchical data for my report.
You may be asking why I need to get hierarchical data when the table
object in the report designer uses a parent-child relationship. The
reason why I'm going through all this pain is because I need to
recursively get all the children from a starting parent level, which
@.OrgNum supplies. SQL Server does not natively support a way to
recursively get all the children in a hierarchy. The only way to do
this is to run through my stored procedure, which recursively calls
itself and then populates a table with the child OrgNum values
(fortunately Yukon has solved this recursive nightmare).
Anyway, how can I generate my report when the error states I must
first declare @.INum?I guess thats because the SPs run independently.
BTW have you considered using cursors in your SP?
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves the
data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use both
the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the query
for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support a
way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||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.
"Ravi" <ravikantkv@.rediffmail.com> wrote in message news:<549501c49175$e1e4dcd0$a601280a@.phx.gbl>...
> I guess thats because the SPs run independently.
> BTW have you considered using cursors in your SP?
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves the
> data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use both
> the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the query
> for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support a
> way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||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!!
>--Original Message--
>I'm having the most difficult time trying to generate a
report that
>first calls a stored procedure and then retrieves the
data produced
>from it.
>I get the error, "An error has occurred during report
>processing...query execution failed for data set
dsOrgs...Must declare
>the variable @.INum" when I try to run the report.
>The dataset below (dsOrgs) first calls a stored procedure
>(SV_GetSubordinates) that populates a table with
hierarchical data.
>The second part (the Select statement) then retrieves
the data
>produced by the stored procedure. I have no problem
running this set
>of SQL statements in the Data view of the Reporting
Services Report
>Designer.
>EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
>SELECT v_Orgs.*
>FROM v_Orgs, SVSiblings
>WHERE
> v_Orgs.INum = @.INum AND
> v_Orgs.INum = SVSiblings.INum AND
> v_Orgs.OrgNum = SVSiblings.Num
>By the way, @.INum is a parameter that will be passed to
the report in
>a URL string eventually. But for now, I have to use
both the Preview
>capability of the Report Designer and the Report Manager
rendering
>engine to test out my report.
>I have another dataset that gets the @.OrgNum parameter
value from a
>selection in a drop down in my report. Here is the
query for that
>data set...
>SELECT
> NULL AS OrgNum,
> '-- ALL Orgs --' AS [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>UNION
>SELECT
> OrgNum,
> [Description]
>FROM SVGroupDefs
>WHERE
> INum = @.INum
>ORDER BY [Description]
>As you can see, I'm using @.INum in this dataset first so
I can
>populate my drop down list. When the user selects an
Organization
>from the drop down list, the selection returns the value
for the
>parameter @.OrgNum, which is used in my dsOrgs dataset
along with @.INum
>to retrieve the hierarchical data for my report.
>You may be asking why I need to get hierarchical data
when the table
>object in the report designer uses a parent-child
relationship. The
>reason why I'm going through all this pain is because I
need to
>recursively get all the children from a starting parent
level, which
>@.OrgNum supplies. SQL Server does not natively support
a way to
>recursively get all the children in a hierarchy. The
only way to do
>this is to run through my stored procedure, which
recursively calls
>itself and then populates a table with the child OrgNum
values
>(fortunately Yukon has solved this recursive nightmare).
>Anyway, how can I generate my report when the error
states I must
>first declare @.INum?
>.
>|||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.
"Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> 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!!
> >--Original Message--
> >I'm having the most difficult time trying to generate a
> report that
> >first calls a stored procedure and then retrieves the
> data produced
> >from it.
> >
> >I get the error, "An error has occurred during report
> >processing...query execution failed for data set
> dsOrgs...Must declare
> >the variable @.INum" when I try to run the report.
> >
> >The dataset below (dsOrgs) first calls a stored procedure
> >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> >The second part (the Select statement) then retrieves
> the data
> >produced by the stored procedure. I have no problem
> running this set
> >of SQL statements in the Data view of the Reporting
> Services Report
> >Designer.
> >
> >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> >SELECT v_Orgs.*
> >FROM v_Orgs, SVSiblings
> >WHERE
> > v_Orgs.INum = @.INum AND
> > v_Orgs.INum = SVSiblings.INum AND
> > v_Orgs.OrgNum = SVSiblings.Num
> >
> >By the way, @.INum is a parameter that will be passed to
> the report in
> >a URL string eventually. But for now, I have to use
> both the Preview
> >capability of the Report Designer and the Report Manager
> rendering
> >engine to test out my report.
> >
> >I have another dataset that gets the @.OrgNum parameter
> value from a
> >selection in a drop down in my report. Here is the
> query for that
> >data set...
> >
> >SELECT
> > NULL AS OrgNum,
> > '-- ALL Orgs --' AS [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >UNION
> >SELECT
> > OrgNum,
> > [Description]
> >FROM SVGroupDefs
> >WHERE
> > INum = @.INum
> >ORDER BY [Description]
> >
> >As you can see, I'm using @.INum in this dataset first so
> I can
> >populate my drop down list. When the user selects an
> Organization
> >from the drop down list, the selection returns the value
> for the
> >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> >to retrieve the hierarchical data for my report.
> >
> >You may be asking why I need to get hierarchical data
> when the table
> >object in the report designer uses a parent-child
> relationship. The
> >reason why I'm going through all this pain is because I
> need to
> >recursively get all the children from a starting parent
> level, which
> >@.OrgNum supplies. SQL Server does not natively support
> a way to
> >recursively get all the children in a hierarchy. The
> only way to do
> >this is to run through my stored procedure, which
> recursively calls
> >itself and then populates a table with the child OrgNum
> values
> >(fortunately Yukon has solved this recursive nightmare).
> >
> >Anyway, how can I generate my report when the error
> states I must
> >first declare @.INum?
> >.
> >|||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.
steve.pantazis@.salesviz.com (Steve Pantazis) wrote in message news:<45c5a039.0409032037.1e978da4@.posting.google.com>...
> 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.
>
> "Melissa" <anonymous@.discussions.microsoft.com> wrote in message news:<028801c491fd$3c204e00$a401280a@.phx.gbl>...
> > 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!!
> > >--Original Message--
> > >I'm having the most difficult time trying to generate a
> report that
> > >first calls a stored procedure and then retrieves the
> data produced
> > >from it.
> > >
> > >I get the error, "An error has occurred during report
> > >processing...query execution failed for data set
> dsOrgs...Must declare
> > >the variable @.INum" when I try to run the report.
> > >
> > >The dataset below (dsOrgs) first calls a stored procedure
> > >(SV_GetSubordinates) that populates a table with
> hierarchical data.
> > >The second part (the Select statement) then retrieves
> the data
> > >produced by the stored procedure. I have no problem
> running this set
> > >of SQL statements in the Data view of the Reporting
> Services Report
> > >Designer.
> > >
> > >EXEC SV_GetSubordinates @.INum,'Groups',@.OrgNum
> > >SELECT v_Orgs.*
> > >FROM v_Orgs, SVSiblings
> > >WHERE
> > > v_Orgs.INum = @.INum AND
> > > v_Orgs.INum = SVSiblings.INum AND
> > > v_Orgs.OrgNum = SVSiblings.Num
> > >
> > >By the way, @.INum is a parameter that will be passed to
> the report in
> > >a URL string eventually. But for now, I have to use
> both the Preview
> > >capability of the Report Designer and the Report Manager
> rendering
> > >engine to test out my report.
> > >
> > >I have another dataset that gets the @.OrgNum parameter
> value from a
> > >selection in a drop down in my report. Here is the
> query for that
> > >data set...
> > >
> > >SELECT
> > > NULL AS OrgNum,
> > > '-- ALL Orgs --' AS [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >UNION
> > >SELECT
> > > OrgNum,
> > > [Description]
> > >FROM SVGroupDefs
> > >WHERE
> > > INum = @.INum
> > >ORDER BY [Description]
> > >
> > >As you can see, I'm using @.INum in this dataset first so
> I can
> > >populate my drop down list. When the user selects an
> Organization
> > >from the drop down list, the selection returns the value
> for the
> > >parameter @.OrgNum, which is used in my dsOrgs dataset
> along with @.INum
> > >to retrieve the hierarchical data for my report.
> > >
> > >You may be asking why I need to get hierarchical data
> when the table
> > >object in the report designer uses a parent-child
> relationship. The
> > >reason why I'm going through all this pain is because I
> need to
> > >recursively get all the children from a starting parent
> level, which
> > >@.OrgNum supplies. SQL Server does not natively support
> a way to
> > >recursively get all the children in a hierarchy. The
> only way to do
> > >this is to run through my stored procedure, which
> recursively calls
> > >itself and then populates a table with the child OrgNum
> values
> > >(fortunately Yukon has solved this recursive nightmare).
> > >
> > >Anyway, how can I generate my report when the error
> states I must
> > >first declare @.INum?
> > >.
> > >