All we need is an easy explanation of the problem, so here it is.
I have a stored procedure that has an output parameter of the
CURSOR VARYING type. I would like to verify that the output cursor can be used by the code that called the stored procedure. It seemed that
CURSOR_STATUS 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; END; GO 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_STATUS return a value of -3 ("A cursor with the specified name does not exist.") inside of the stored procedure?
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
The cursor isn’t assigned to the ‘variable’
@Cursor_OUT until copy-out at the end of the procedure.
Before then, it isn’t a ‘cursor variable’ and therefore not visible to
CURSOR_STATUS or system stored procedures like
That said, the name of the cursor is
@Cursor_OUT and that is visible through sys.dm_exec_cursors.
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; END;
You could also assign the
@Cursor_OUT earlier and use that in the
OPEN call. 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.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂