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

Question

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?

Answer 1

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 sp_describe_cursor.

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