Sql-server – Stored Procedure return unwanted cursor result

azure-sql-databasecursorsmemory-optimized-tablessql serverstored-procedures

Context

I have created a stored procedure that simulates a merge command for an OLTP in-memory table. It used the simulated merge for upsert moving massive data form a normal table to an OLTP one.

Procedure

This is the code:

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1; 
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor; 
    WHILE @@FETCH_STATUS = 0
    BEGIN
        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;

        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0  
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status);  
    END
      
    CLOSE Employee_Cursor;  
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

The cursor upserts a range of rows like this because OLTP tables (target) do not support MERGE commands.

Problem

If I execute my SP I will get a result the first cursor selected FETCH and a list of single row counts.

table result

message

Question

I understand that this is normal behavior, but I try to suppress this to get a simpler result.

What I want:

  1. No table result from CURSOR select
  2. A sum of affected row count as single number of all update/insert operations

The empty FETCH was indeed an error and solves the ROW result problem (point 1).

Looks like this now:

CREATE PROCEDURE [cache].[MoveInverterData] (@sourecInverterID bigint, @from datetime2(7))
AS   
BEGIN
    SET NOCOUNT ON;

    DECLARE @i INT = 1; 
    DECLARE @InverterID bigint, @Timestamp datetime2(7), @Status nvarchar(50);

    DECLARE Employee_Cursor CURSOR READ_ONLY FOR  
        SELECT [InverterID],[Timestamp],[Status]
        FROM [data].[InverterData]
        WHERE [InverterID] = @sourecInverterID AND [Timestamp] >= @from;  

    OPEN Employee_Cursor;

    FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE [cache].[InverterData]
        SET [Status] = @Status
        WHERE [InverterID] = @InverterID AND [Timestamp] = @Timestamp;  

        -- if there was no row to update, insert
        IF @@ROWCOUNT=0  
            INSERT INTO [cache].[InverterData]
                ([InverterID],[Timestamp],[Status])
            VALUES
                (@InverterID, @Timestamp, @Status); 

        FETCH NEXT FROM Employee_Cursor INTO @InverterID, @Timestamp, @Status;
    END
      
    CLOSE Employee_Cursor;  
    DEALLOCATE Employee_Cursor;

    RETURN(0)
END

Best Answer

Instead of using a cursor*, write the update and insert operations explicitly as set-based operations:

CREATE OR ALTER PROCEDURE cache.MoveInverterData
(
    @sourceInverterID bigint, 
    @from datetime2(7)
)
AS   
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    DECLARE @RowsAffected integer = 0;

    -- TODO: Add error handling

    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    BEGIN TRANSACTION;

        -- Update existing rows
        UPDATE CID
        SET [Status] = DID.[Status]
        FROM [data].InverterData AS DID
        JOIN cache.InverterData AS CID
                WITH (SNAPSHOT)
            ON CID.InverterID = DID.InverterID
            AND CID.[Timestamp] = DID.[Timestamp]
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from;

        SET @RowsAffected += @@ROWCOUNT;

        -- Insert new rows
        INSERT cache.InverterData
            (InverterID, [Timestamp], [Status])
        SELECT
            DID.InverterID,
            DID.[Timestamp],
            DID.[Status]
        FROM [data].InverterData AS DID
        WHERE
            DID.InverterID = @sourceInverterID
            AND DID.[Timestamp] >= @from
            AND NOT EXISTS
            (
                SELECT 1
                FROM cache.InverterData AS CID
                    WITH (SNAPSHOT)
                WHERE
                    CID.InverterID = DID.InverterID
                    AND CID.[Timestamp] = DID.[Timestamp]
            );

        SET @RowsAffected += @@ROWCOUNT;

    COMMIT TRANSACTION;

    -- TODO: Use the @RowsAffected result
END;

Ensure both tables have a (non-hash) index on InverterID, [Timestamp].

* Unlike some other database engines, cursors are usually quite inefficient in SQL Server. The overhead and per-row operations almost always make a set-based solution faster and more efficient.

Related Question