Sharing Data Between Procedures using an Input Cursor and an Output API Cursor

sharing_data_cursor_api.sql

-- Passing data into and out of modules with cursors
-- Cursor variables are NOT output only
-- API cursor used for OUTPUT

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

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

BEGIN TRY
    DECLARE 
        -- Cursor will be automatically closed and
        -- deallocated when the variable goes out of scope
        @Products cursor,
        -- API cursor handle
        @API integer;

    -- Cursor definition
    SET @Products = 
        CURSOR LOCAL
        SCROLL
        DYNAMIC
        SCROLL_LOCKS
        TYPE_WARNING
    FOR
        -- The cursor query would usually be more 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 variable
        @Products,
        -- OUTPUT API cursor handle
        @API OUTPUT;

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

    -- Populate results in a single API cursor fetch
    INSERT @Results
    EXECUTE sys.sp_cursorfetch
        @API,
        1, -- FIRST
        0, -- row num (unused)
        2147483647; -- nrows

    -- Close the API cursor
    EXECUTE sys.sp_cursorclose
        @API;

    -- Show results of the processing
    SELECT 
        ProcessedStatus, 
        NumRows = COUNT_BIG(*)
    FROM @Results AS R
    GROUP BY 
        ProcessedStatus
    ORDER BY
        ProcessedStatus;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

    -- Close the API cursor
    IF @API <> 0
    BEGIN
        EXECUTE sys.sp_cursorclose
            @API;
    END;

    THROW;
END CATCH;
GO

-- Only ProcessedStatus actions shown
CREATE PROCEDURE dbo.ProcessFinishedGoods
    -- INPUT cursor parameter!
    @InProducts CURSOR VARYING OUTPUT,
    -- OUTPUT API cursor
    @OutReturn integer 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
    -- Working variables
    DECLARE 
        @ProductID integer,
        @ProcessedStatus tinyint;

    -- Results
    CREATE TABLE #ProcessFinishedGoods_Results
    (
        ProductID integer NOT NULL PRIMARY KEY,
        ProcessedStatus tinyint NOT NULL
    );

    -- 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 #ProcessFinishedGoods_Results
                (ProductID, ProcessedStatus)
            VALUES
                (@ProductID, @ProcessedStatus);
        END;

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

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

    -- Suppress result set
    INSERT TOP (0) 
        @Dummy
    -- Capture results in a STATIC API cursor
    EXECUTE sys.sp_cursoropen
        @OutReturn OUTPUT,
        -- Notice we are returning the contents of a
        -- local temporary table.
        --
        -- OPTION (RECOMPILE) added to prevent optional_spid
        -- cache littering due to accessing a temporary table
        -- outside the scope in which it was created.
        N'
        SELECT 
            ProductID, 
            ProcessedStatus 
        FROM #ProcessFinishedGoods_Results
        OPTION 
            (RECOMPILE);
        ',
        8, -- STATIC
        1; -- READ_ONLY
END;
GO

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

SET XACT_ABORT, NOCOUNT ON;

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

EXECUTE dbo.DoStuff;

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