Passing Cursor as SP output parameter

Hi,
I have a SP that uses a temporary table and cursor to give all employee IDs
for a passwed managerid at all level of a hierarchy using a structuretable
that has parentId and childId. The SP runs successfully, but when I am
trying to use the output cursor I get the following error:
Calling SQL:
DECLARE @.childCursor cursor
EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
FETCH @.childCursor
CLOSE @.childCursor
DEALLOCATE @.ChildCursor
output:
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 4
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Server: Msg 16950, Level 16, State 2, Line 5
The variable '@.childCursor' does not currently have a cursor allocated to it
.
Any help on this problem will be greatly appreciated.
SangDid you define the childCuror variable as an OUT parm in the proc?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
> Hi,
> I have a SP that uses a temporary table and cursor to give all employee ID
s
> for a passwed managerid at all level of a hierarchy using a structuretable
> that has parentId and childId. The SP runs successfully, but when I am
> trying to use the output cursor I get the following error:
> Calling SQL:
> DECLARE @.childCursor cursor
> EXEC dco.getPersonIdChild 'EMGR', 'STR', 6852, @.childCursor OUT
> FETCH @.childCursor
> CLOSE @.childCursor
> DEALLOCATE @.ChildCursor
> output:
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 4
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Server: Msg 16950, Level 16, State 2, Line 5
> The variable '@.childCursor' does not currently have a cursor allocated to
it.
> Any help on this problem will be greatly appreciated.
> Sang
>|||Yes, please see the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
"Tibor Karaszi" wrote:

> Did you define the childCuror variable as an OUT parm in the proc?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:C7D4D59F-3E84-42BC-B132-67DAF449AB6B@.microsoft.com...
>|||I think you need to give us more to go on. Below work fine for me:
USE pubs
GO
CREATE PROC p @.c cursor varying OUT
AS
SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
OPEN @.c
GO
DECLARE @.oc cursor
EXEC p @.c = @.oc OUTPUT
FETCH NEXT FROM @.oc
GO
DROP PROC p
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sang" <Sang@.discussions.microsoft.com> wrote in message
news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
> Yes, please see the following:
> ALTER PROCEDURE dco.getPersonIdChild (
> @.Code CodeExtraLong,
> @.CodeTypeCode CHAR(3),
> @.PersonIdParent UniqueId,
> @.childCursor CURSOR VARYING OUT
> )
> AS
> "Tibor Karaszi" wrote:
>|||The SP is the following:
ALTER PROCEDURE dco.getPersonIdChild (
@.Code CodeExtraLong,
@.CodeTypeCode CHAR(3),
@.PersonIdParent UniqueId,
@.childCursor CURSOR VARYING OUT
)
AS
BEGIN
DECLARE
@.Level smallint,
@.PersonIdParentC UniqueID
-- Let the intial set of children for the passed parentId
SET @.Level = 1
IF OBJECT_ID ('#tempTable') IS NOT NULL
Drop Table #tempTable
CREATE TABLE #TempTable (PersonIdChild UniqueId, Depth smallint, Checked
bit)
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level AS "Depth", 0 AS "Checked"
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParent
AND EndDate is NULL
WHILE ((SELECT count(*) from #TempTable WHERE Checked = 0 ) > 0)
BEGIN
DECLARE Children CURSOR
FOR
SELECT PersonIdChild
FROM #TempTable
WHERE Depth = @.Level
OPEN Children
FETCH Children into @.PersonIdParentC
WHILE (@.@.FETCH_STATUS =0)
BEGIN
-- PRINT '@.Level is ' + CAST(@.Level AS CHAR)
IF ((SELECT Count(*) from PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL) > 0 )
BEGIN
INSERT INTO #TempTable
SELECT PersonIdChild, @.Level + 1, 0
FROM PersonStructures
WHERE Code = @.Code
AND CodeTypeCode = @.CodeTypeCode
AND PersonIdParent = @.PersonIdParentC
AND EndDate is NULL
END
-- Now Update the checked column for the record just processed
UPDATE #TempTable
SET Checked = 1
WHERE PersonIdChild = @.PersonIdParentC
FETCH NEXT FROM Children INTO @.PersonIdParentC
END
CLOSE Children
DEALLOCATE Children
SET @.Level = @.Level + 1
END
DECLARE s CURSOR
LOCAL
FOR SELECT PersonIdChild, Depth
FROM #TempTable
SET @.childCursor = s
OPEN @.childCursor
RETURN (0)
END
"Tibor Karaszi" wrote:

> I think you need to give us more to go on. Below work fine for me:
> USE pubs
> GO
> CREATE PROC p @.c cursor varying OUT
> AS
> SET @.c = CURSOR LOCAL FOR SELECT au_lname FROM authors
> OPEN @.c
> GO
> DECLARE @.oc cursor
> EXEC p @.c = @.oc OUTPUT
> FETCH NEXT FROM @.oc
> GO
> DROP PROC p
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sang" <Sang@.discussions.microsoft.com> wrote in message
> news:1E16242A-4AD9-4F61-8781-13F833E85369@.microsoft.com...
>sql

pass data between two datasets

Is there anyway i can pass data between two datasets
ex:
dataset 1 has
select empid, fname,lname ,salary from employee
dataset2 has
select userid ,total/(dataset1.salary) from anothertable
here i want to use a value from dataset 1 in dataset 2.
Thanks in advnceLook into using subreports.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Sub reports ? but i dont want to go into the front end. i want to do it in
the backend itself.
you know pass data from one data set to another directly
Thanks
"Bruce L-C [MVP]" wrote:
> Look into using subreports.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
> > Is there anyway i can pass data between two datasets
> >
> > ex:
> > dataset 1 has
> > select empid, fname,lname ,salary from employee
> >
> > dataset2 has
> > select userid ,total/(dataset1.salary) from anothertable
> >
> > here i want to use a value from dataset 1 in dataset 2.
> >
> > Thanks in advnce
>
>|||You can embed a sub report into the table of the first report passing it the
value from the field of the first one (the subreport should have a
parameter).
There is no way from within RS to tie one dataset to another. Either use
subreports or put the logic in a stored procedure (but since a stored
procedure can only return a single resultset, or rather RS will only use a
single resultset from a stored procedure) I don't think a stored procedure
will work for you.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ramani" <Ramani@.discussions.microsoft.com> wrote in message
news:B4ED0F7F-4CCE-4484-B21F-8DD2B84EF0A3@.microsoft.com...
> Sub reports ? but i dont want to go into the front end. i want to do it in
> the backend itself.
> you know pass data from one data set to another directly
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Look into using subreports.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Ramani" <Ramani@.discussions.microsoft.com> wrote in message
>> news:DECA721E-7B10-4D19-B57C-BA92D951453F@.microsoft.com...
>> > Is there anyway i can pass data between two datasets
>> >
>> > ex:
>> > dataset 1 has
>> > select empid, fname,lname ,salary from employee
>> >
>> > dataset2 has
>> > select userid ,total/(dataset1.salary) from anothertable
>> >
>> > here i want to use a value from dataset 1 in dataset 2.
>> >
>> > Thanks in advnce
>>|||Ramani,
You really need to get all the data you want for a data region into one
dataset. You need to redo the query, there must be a relationship
between ds1 and ds2, or if it's just a continuous list of data try the
Union command.
Sub-reports require much greater overhead it's better not to use them
in this scenario.
Maybe try something like this;
Select e.empid, e.fname, e.lname, e.salary, a.userid, a.total
from employee e
join anothertable a on e.empid = a.userid
It may generate lots of repeated data, but RS can deal with this much
more efficiently than using sub-reports.
Chris
Ramani wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce|||Easiest way may be to create a view between the 2.
"Ramani" wrote:
> Is there anyway i can pass data between two datasets
> ex:
> dataset 1 has
> select empid, fname,lname ,salary from employee
> dataset2 has
> select userid ,total/(dataset1.salary) from anothertable
> here i want to use a value from dataset 1 in dataset 2.
> Thanks in advnce