Sharing Data Between Procedures using a Cursor

Introduction

Unlike programming languages with native list sharing, SQL Server often faces challenges with multi-row data sharing among code modules.

The most comprehensive article on the topic is Erland Sommarskog’s How to Share Data between Stored Procedures. As usual for Erland’s long-form works, he addresses all the major alternatives in great detail and describes the advantages and disadvantages of each very well.

One section that is unusually short is the one on Using Cursor Variables. In fairness, Erland does say he never uses this method himself and it was only added to the article at a reader’s suggestion.

Still, the cursor variable method is described as only being capable of providing an output result set from a procedure, which isn’t strictly true. The overall summary for the cursor variable option is a simple “Not recommendable”, which seems a little harsh.

Why use a Cursor Variable?

I certainly won’t be recommending this option as a primary method, but it may have a place in the advanced developer’s toolkit.

As an output

The documentation describes using a cursor as an output reasonably well and even includes some code examples:

CREATE PROCEDURE dbo.uspCurrencyCursor
    @CurrencyCursor CURSOR VARYING OUTPUT
AS
    SET NOCOUNT ON;
    SET @CurrencyCursor = CURSOR
    FORWARD_ONLY STATIC FOR
      SELECT CurrencyCode, Name
      FROM Sales.Currency;
    OPEN @CurrencyCursor;
GO

There are further usage examples in Return data from a stored procedure and in the cursor stored procedures documentation:

• sp_cursor_list
• sp_describe_cursor
• sp_describe_cursor_tables
• sp_describe_cursor_columns

Using a cursor variable as an output offers flexibility over the shape of the returned result set, but I wouldn’t exactly describe it as convenient in practice. I can quite see why people would often prefer to return a table or structured string like XML or JSON instead.

Still, consuming the output row-by-row using the supplied cursor variable may be convenient in some situations.

As an input

The documentation states that cursor type parameters can only be output:

Transact-SQL procedures can use the cursor data type only for output parameters. If the cursor data type is specified for a parameter, both the varying and output keywords must be specified for that parameter in the procedure definition. A parameter can be specified as only output, but if the varying keyword is specified in the parameter declaration, the data type must be cursor and the output keyword must also be specified.

While the procedure parameter must indeed have CURSOR VARYING OUTPUT, this does not completely preclude providing a cursor variable as an input parameter.

Normally, one would provide an empty cursor variable at the call site, also specifying the OUTPUT keyword there (not VARYING). For example:

DECLARE @c cursor;
EXECUTE dbo.Proc @c OUTPUT;

However, you can pass a cursor variable with a cursor assigned to it as an input parameter by omitting the OUTPUT keyword at the call site. I’ll show this in an example shortly.

Row-by-row

Perhaps the biggest criticism of T-SQL cursors is that they enforce row-by-row processing. This is a fair observation, but the fact is that many real-world procedures end up processing their input row-by-row anyway.

It’s not at all unusual for a top-level module to call other functions and procedures that are too complex to implement in a set-based way. This typically means passing in one row at a time in parameters.

Table-valued parameters, (temporary) tables, or structured strings (like XML or JSON) can work well for input. However, if row-by-row processing is ultimately required, you’ll still need a cursor or loop. Shredding the input into a suitable form for looping is simply extra work in this scenario.

While often slower than set-based operations overall, passing a reference to a cursor as a input parameter can avoid unnecessary copying of large structured strings.

Let’s look at a complete implementation now, using cursor types for input and output stored procedure parameters.

Demo

Using an AdventureWorks sample database and any version of SQL Server, the following script resets the environment and populates a new table of products ready for processing:

-- Any version will do
USE AdventureWorks2022;
GO

DROP PROCEDURE IF EXISTS 
    dbo.ComputeProcessedStatus,
    dbo.ProcessFinishedGoods,
    dbo.DoStuff;

DROP TABLE IF EXISTS dbo.FinishedGoods;
GO

CREATE TABLE dbo.FinishedGoods
(
    ProductID integer NOT NULL PRIMARY KEY,
    ProcessedStatus tinyint NOT NULL DEFAULT 0
);
GO

INSERT dbo.FinishedGoods 
    (ProductID)
SELECT 
    P.ProductID
FROM Production.Product AS P
WHERE 
    P.FinishedGoodsFlag = CONVERT(bit, 'true');
GO

My example uses three nested procedures, reflecting a fairly common pattern. Let’s look at each in the order they are called.

Outer procedure

The top-level procedure runs the business process, acquiring updated product processing state as a small part of the overall logic.

The activity of interest to us is opening a local T-SQL cursor over the products to process and passing it to a second procedure as an input parameter:

-- Does many crucial things
-- Only ProcessFinishedGoods shown for brevity
CREATE PROCEDURE dbo.DoStuff
AS
SET NOCOUNT, XACT_ABORT ON;
SET STATISTICS XML OFF;

-- Cursor will be automatically closed and
-- deallocated when the variable goes out of scope
DECLARE 
    @Products cursor,
    @Return cursor;

-- Cursor definition
SET @Products = 
    CURSOR LOCAL
    SCROLL
    DYNAMIC
    SCROLL_LOCKS
    TYPE_WARNING
FOR
    -- The cursor query would usually be complex
    SELECT 
        ProductID
    FROM dbo.FinishedGoods
    WHERE 
        ProcessedStatus = 0
    ORDER BY 
        ProductID ASC;

-- Open the cursor
OPEN @Products;

-- Perform complicated finished goods processing
-- with the cursor variable as input parameter
EXECUTE dbo.ProcessFinishedGoods 
    -- INPUT cursor!
    @Products,
    -- OUTPUT cursor
    @Return OUTPUT;

-- Accumulates @Return cursor results
DECLARE @Results table 
(
    ProductID integer NOT NULL PRIMARY KEY,
    ProcessedStatus tinyint NOT NULL
);

-- Cursor fetch variables
DECLARE 
    @ProductID integer,
    @ProcessedStatus tinyint;

-- Ensure the cursor starts at the beginning
FETCH FIRST 
FROM @Return 
INTO @ProductID, @ProcessedStatus;

-- Cursor reading loop
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @Results 
        (ProductID, ProcessedStatus)
    VALUES 
        (@ProductID, @ProcessedStatus);

    FETCH NEXT 
    FROM @Return
    INTO @ProductID, @ProcessedStatus;
END;

-- Show results of the processing
SELECT 
    ProcessedStatus, 
    NumRows = COUNT_BIG(*)
FROM @Results AS R
GROUP BY 
    ProcessedStatus
ORDER BY
    ProcessedStatus;
GO

Middle layer

The second procedure iterates over the input cursor reference, calling a third procedure for each row. It returns its results using a second output cursor:

-- Only ProcessedStatus actions shown
CREATE PROCEDURE dbo.ProcessFinishedGoods
    -- INPUT cursor parameter!
    @InProducts CURSOR VARYING OUTPUT,
    -- OUTPUT cursor
    @OutReturn CURSOR VARYING OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;
SET STATISTICS XML OFF;

-- Workaround reference for CURSOR_STATUS call
DECLARE @Ref cursor; 
SET @Ref = @InProducts;

IF CURSOR_STATUS('variable', N'@Ref') > 0
BEGIN
    -- Just to show this works without 
    -- affecting the passed-in reference
    DEALLOCATE @Ref;

    -- Results
    DECLARE @Results table 
    (
        ProductID integer NOT NULL PRIMARY KEY,
        ProcessedStatus tinyint NOT NULL
    );

    -- Working variables
    DECLARE 
        @ProductID integer,
        @ProcessedStatus tinyint;

    -- Ensure the cursor is at the start
    FETCH FIRST 
    FROM @InProducts
    INTO @ProductID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- Get the new status
        EXECUTE dbo.ComputeProcessedStatus
            @ProductID = @ProductID,
            @ProcessedStatus = @ProcessedStatus OUTPUT;
                
        -- Only add non-NULL processed status rows to the results
        IF @ProcessedStatus IS NOT NULL
        BEGIN
            INSERT @Results
                (ProductID, ProcessedStatus)
            VALUES
                (@ProductID, @ProcessedStatus);
        END;

        -- Next product
        FETCH NEXT 
        FROM @InProducts
        INTO @ProductID;
    END;

    -- Return results as a cursor
    SET @OutReturn = CURSOR LOCAL
        SCROLL
        STATIC
        READ_ONLY
    FOR
        -- Notice we are returning the
        -- contents of a local table variable
        SELECT 
            ProductID, 
            ProcessedStatus 
        FROM @Results;

    -- OUTPUT cursor must be OPEN before returning
    OPEN @OutReturn;
END;
GO

Each row is processed and only added to the results if the computed status is not NULL.

None of the demo logic is so complex that it couldn’t be implemented as a single set-based statement. Real procedures of this type often contain thousands of lines of procedural code, making row-by-row processing a practical necessity.

A small bug

The main wrinkle with the middle procedure is in the CURSOR_STATUS() call: The input cursor parameter is not visible to this function (or the cursor extended stored procedures mentioned earlier).

This behaviour appears to be an unintended consequence of the way OUTPUT parameters are assigned in SQL Server. To the best of my knowledge, this quirk hasn’t been documented before. This is perhaps unsurprising since it is not even widely appreciated that cursor variables can be used as inputs.

The input cursor is displayed in sys.dm_exec_cursors, but I chose not to use that DMV here because it requires VIEW SERVER STATE permission (or VIEW SERVER PERFORMANCE STATE from SQL Server 2022).

Workaround

The simple workaround is to declare a local cursor variable and assign the input cursor parameter to that. Note that this does not create a new cursor—it simply creates a new pointer to the existing input cursor.

The new reference can be successfully used with CURSOR_STATUS() and other intrinsics as needed. It will go out of scope at the end of the module, but can also be explicitly deallocated (as shown in the demo) without affecting the underlying cursor.

The T-SQL FETCH command is quite happy to use the @Products input cursor parameter directly. You could change this to reference the local cursor variable pointer for consistency as you prefer. Remember to remove the deallocation statement if you do though!

Detail layer

The final procedure determines the current status for a particular product and returns that in an output parameter:

-- Compute the ProcessedStatus of a Product
-- This is a complicated process that cannot
-- be implemented as a function
CREATE PROCEDURE dbo.ComputeProcessedStatus
    @ProductID integer,
    @ProcessedStatus tinyint OUTPUT
AS
SET NOCOUNT, XACT_ABORT ON;

DECLARE
    @ActualCost money;

SET @ActualCost =
(
    SELECT 
        TH.ActualCost
    FROM Production.TransactionHistory AS TH
    WHERE
        TH.ProductID = @ProductID
        AND TH.ActualCost > $5
    ORDER BY
        TH.ActualCost DESC
        OFFSET 0 ROWS
        FETCH FIRST 1 ROW ONLY
);

SET @ProcessedStatus =
    CASE
        WHEN @ActualCost IS NULL THEN NULL
        WHEN @ActualCost < $10 THEN 1
        WHEN @ActualCost < $25 THEN 2
        WHEN @ActualCost < $50 THEN 3
        WHEN @ActualCost < $100 THEN 4
        ELSE 9
    END;
GO

There is nothing particularly notable about this procedure. I include it only to complete the demo.

Running the demo

The following code will run the demo and show a summary of the results:

SET XACT_ABORT, NOCOUNT ON;

-- Run the business
DECLARE @Start datetime = CURRENT_TIMESTAMP;

EXECUTE dbo.DoStuff;

SELECT [Elapsed ms] = 
    DATEDIFF(MILLISECOND, @Start, CURRENT_TIMESTAMP);

For convenience, the whole script is available in this gist.

Results

The results represent the distribution of values in the sample database and have no deeper meaning.

Performance is respectable at around 150ms with execution plans turned off on my local SQL Server 2022 CU18 instance.

Other Details

While not strictly relevant to the main point of this article, there are a couple of other items I want to address briefly for the interested reader.

The return cursor

You may have noticed that the output cursor in the middle procedure is defined over a table variable.

You might expect the cursor to be unusable by the outer procedure because the table variable source has gone out of scope and been destroyed.

The reason this doesn’t happen is because the cursor is defined as STATIC. This type of cursor stores its entire result in a tempdb worktable, which is unaffected by the table variable’s expiration:

Execution plan showing table variable contents being written to the static cursor’s worktable

Trying to use a DYNAMIC or KEYSET cursor here would fail with an error. These cursor types need to access the underlying object on each FETCH, which isn’t possible when that object (the table variable) no longer exists.

Input cursor history

It was not possible to pass input cursor parameters in SQL Server 2000. Trying to do so returns error 16951:

The variable '%.*ls' cannot be used as a parameter because a CURSOR OUTPUT parameter must not have a cursor allocated to it before execution of the procedure.

The same operation succeeds in SQL Server 2005 and later.

I have a faint recollection that this change was made to allow cursor variables to work with dynamic SQL. It seems the documentation was never updated to make this explicit or provide example usage.

Testing shows that sp_executesql fails with a cursor variable parameter in SQL Server 2000 but succeeds in SQL Server 2005. Yes, I still have those versions installed.

Rewriting the demo

You might object that the outer cursor in the demo could simply be moved into the middle procedure, doing away with the need to pass cursors around. This is a fair point, but remember the demo is highly simplified.

In a real implementation, the outer procedure would generate the input cursor set using complicated logic. It is simply the job of the outer procedure to produce that set.

The middle procedure is only concerned with iterating over the supplied rows, applying whatever business rules are necessary at that level, and calling the lowest level procedure for each row.

Using an output API cursor

It would be more efficient to implement the output cursor as an API cursor. This allows the result to be consumed all-at-once rather than row-by-row.

A modified demo using an API cursor for output is available in this gist.

Final Thoughts

A T-SQL cursor variable can be used either as an input or an output stored procedure parameter (but not both). Despite the documentation only showing examples of it being used to provide a flexible output, the input usage seems more compelling to me.

It is a shame that T-SQL cursors don’t support fetching more than one row at a time. This would make their use as output parameters more practical. There’s no particular reason to use a T-SQL cursor for the output in the demo. One might choose any of the other data set returning methods described by Erland.

Most operations are better as set-based implementations, but as the documentation acknowledges, this isn’t always practical or desirable:

Microsoft SQL Server statements produce a complete result set, but there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

If you need to perform row-by-row processing in a stored procedure anyway, consider providing the data in an input cursor type variable to avoid unnecessary serialization and shredding activity.

All methods for sharing data between modules have advantages and disadvantages. Refer to Erland’s article for the details. Consider supplementing the cursor variables section with this article’s insights.

Thanks for reading.