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



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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2