Why does CURSOR_STATUS return unexpected results for an output cursor inside the stored procedure that created it?
I have a stored procedure that has an output parameter of the
CURSOR VARYINGtype. I would like to verify that the output cursor can be used by the code that called the stored procedure. It seemed that https://docs.microsoft.com/en-us/sql/t-sql/functions/cursor-status-transact-sql?view=sql-server-ver16 was the right function to use, but I'm getting unexpected results when applying it to my output cursor. The function returns a value of -3 inside the stored procedure that created it but works as expected outside the stored procedure. See the code below:
CREATE OR ALTER PROCEDURE dbo.OutputCursorTest (@Cursor_OUT CURSOR VARYING OUTPUT) AS BEGIN SET NOCOUNT ON;
SET @Cursor_OUT = CURSOR FORWARD_ONLY STATIC FOR SELECT [high] from master..spt_values OPEN @Cursor_OUT; SELECT CURSOR_STATUS('variable', '@Cursor_OUT'); -- this seems to always return -3 -- possible workaround /* DECLARE @Cur_Copy CURSOR; SET @Cur_Copy = @Cursor_OUT; SELECT CURSOR_STATUS('variable', '@Cur_Copy'); DEALLOCATE @Cur_Copy; */ RETURN;
DECLARE @Cur CURSOR;
EXEC dbo.OutputCursorTest @Cursor_OUT = @Cur OUTPUT;
SELECT CURSOR_STATUS('variable', '@Cur'); -- this returns 1 as expected
I am on SQL Server 2019 CU14 if it matters. Why does
CURSOR_STATUSreturn a value of -3 ("A cursor with the specified name does not exist.") inside of the stored procedure?
The cursor isn't assigned to the 'variable'
@Cursor_OUTuntil https://docs.microsoft.com/en-gb/archive/blogs/sqlprogrammability/tsql-basics-ii-parameter-passing-semantics at the end of the procedure.
Before then, it isn't a 'cursor variable' and therefore not visible to
CURSOR_STATUSor system stored procedures like https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-describe-cursor-transact-sql .
That said, the name of the cursor is
@Cursor_OUTand that is visible through https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-exec-cursors-transact-sql .
You can also create the cursor using non-variable syntax:
CREATE OR ALTER PROCEDURE dbo.OutputCursorTest @Cursor_OUT CURSOR VARYING OUTPUT AS BEGIN SET NOCOUNT ON;
DECLARE c CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR SELECT [high] FROM master..spt_values; OPEN c; -- SELECT EC.* FROM sys.dm_exec_cursors(@@SPID) AS EC; SELECT CURSOR_STATUS('local', N'c'); -- Assign output SET @Cursor_OUT = c; RETURN;
You could also assign the
@Cursor_OUTearlier and use that in the
OPENcall. It's just a pointer to the 'real' cursor.
One advantage to using the syntax above is it allows you to specify
GLOBAL. That isn't available with the variable form; the type of cursor you get is determined by the database option
CURSOR_DEFAULT. As often the case, being explicit can prevent surprises.