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