Why does CURSOR_STATUS return unexpected results for an output cursor inside the stored procedure that created it?

[obi]

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 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;

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?

[Rossere]

The cursor isn't assigned to the 'variable' @Cursor_OUT until 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_STATUS or 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_OUT and 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;

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 LOCAL or 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.