CURSOR AS PROCEDURE OUTPUT

I've written this GenericCursor procedure that return a CURSOR as output parameter, where the CURSOR is opened by a dynamic SQL statement executed via sp_executesql:

CREATE PROCEDURE dbo.GenericCursor
@.genericCursor CURSOR VARYING OUTPUT
, @.CMD Nvarchar(1024)
AS
BEGIN
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMD,
N'@.utentiCursor CURSOR out',
@.genericCursor out
END

Everityng works fine: I want to use GenericCursor procedure and it works, but when I try to CLOSE and then DEALLOCATE the CURSOR it raises an error (as in the example):

CREATE PROCEDURE testGenericCursor
AS
BEGIN
DECLARE @.MyCursor CURSOR;
DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SELECT idUtente FROM Utenti'
EXEC dbo.UtentiCursor @.MyCursor OUTPUT, @.CMDx;
WHILE (@.@.FETCH_STATUS = 0)
BEGIN;
FETCH NEXT FROM @.MyCursor;
END;
-- CLOSE @.MyCursor;
-- DEALLOCATE @.MyCursor;
END


Why CLOSE and DEALLOCATEare not usable? They generate the following errors:

Msg 16950, Level 16, State 2, Line 13
The variable '@.MyCursor' does not currently have a cursor allocated to it.
Msg 16950, Level 16, State 2, Line 14
The variable '@.MyCursor' does not currently have a cursor allocated to it.

I don't think you ever put the cursor into your output parameter in the first place (unless you just made a copy\paste error when putting your code into the post).

The problem is here:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;' <<<< This won't work (see below)
exec sp_executesql @.CMD, <<<< Shouldn't this be @.CMDx?

Your going to have to pass @.genericCursor into sp_executesql as an output parameter as well, something like this:

DECLARE @.CMDx Nvarchar(1024);
SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'
exec sp_executesql @.CMDx, N'@.genericCursor cursor varying output', @.genericCursor output

|||

The CLOSE and DEALLOCATE are in a different scope than the cursor...they don't know each other exist.

Your code revised:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMD,

N'@.utentiCursor CURSOR out',

@.genericCursor out

CLOSE @.genericCursor

DEALLOCATE @.genericCursor

END

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor;

END

END

EXECdbo.testGenericCursor

This is an example from the EXECUTE (Transact-SQL) topic in Books Online that uses EXEC:

http://msdn2.microsoft.com/en-us/library/ms188332.aspx

USE AdventureWorks;

GO

DECLARE tables_cursor CURSOR

FOR

SELECT s.name, t.name

FROM sys.objects AS t

JOIN sys.schemas AS s ON s.schema_id = t.schema_id

WHERE t.type = 'U';

OPEN tables_cursor;

DECLARE @.schemaname sysname;

DECLARE @.tablename sysname;

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

WHILE (@.@.FETCH_STATUS <> -1)

BEGIN;

EXEC ('ALTER INDEX ALL ON ' + @.schemaname + '.' + @.tablename + ' REBUILD;');

FETCH NEXT FROM tables_cursor INTO @.schemaname, @.tablename;

END;

PRINT 'The indexes on all tables have been rebuilt.';

CLOSE tables_cursor;

DEALLOCATE tables_cursor;

GO

A good link for dynamic SQL:

http://www.sommarskog.se/dynamic_sql.html#cursor0

|||He is using cursor variables, which can be passed between scopes just like he is doing in dbo.GenericCursor. He just has to modify his sp_executesql call to pass the cursor variable as an output parameter.|||

Thanks to evryone out there: I noticed I've posted the wrong code :-P

> SO THIS MEANS YOU ARE SO GOOD THAT YOU'VE UNDERSTOOD IT EVEN IF IT WAS WRONG!!!

Just to summarize what the matter is, this is the final working version:

CREATE PROCEDURE dbo.GenericCursor

@.genericCursor CURSOR VARYING OUTPUT

, @.CMD Nvarchar(1024)

AS

BEGIN

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SET @.genericCursor = CURSOR FORWARD_ONLY STATIC FOR ' + @.CMD + '; OPEN @.genericCursor;'

exec sp_executesql @.CMDx,

N'@.genericCursor cursor output',

@.genericCursor out

END

--

CREATE PROCEDURE testGenericCursor

AS

BEGIN

DECLARE @.MyCursor CURSOR;

DECLARE @.name as varchar(100);

DECLARE @.CMDx Nvarchar(1024);

SET @.CMDx = 'SELECT TOP 50 name FROM dbo.sysobjects;'

EXEC dbo.GenericCursor @.MyCursor OUTPUT, @.CMDx;

FETCH NEXT FROM @.MyCursor INTO @.name;

WHILE (@.@.FETCH_STATUS = 0)

BEGIN;

FETCH NEXT FROM @.MyCursor INTO @.name;

SELECT @.name

END

CLOSE @.MyCursor

DEALLOCATE @.MyCursor

END

--

EXEC dbo.testGenericCursor