Error Handling In SQL Server Cursors

Posted on by Erik Darling

Spit My Last Error

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that and need to solve performance problems quickly.

If you wanna play with the code I used to try things out on your own, here it is:

CREATE TABLE zero_ten (id INT NOT NULL PRIMARY KEY);

INSERT dbo.zero_ten ( id )
SELECT 0 AS id
UNION ALL 
SELECT TOP (10) ROW_NUMBER() OVER(ORDER BY 1/0)
FROM sys.messages AS m

SELECT * FROM dbo.zero_ten AS zt;
RETURN;

DECLARE c CURSOR LOCAL STATIC FOR
SELECT N'DECLARE @atmosphere INT; SELECT @atmosphere = 1/' + RTRIM(zt.id)
FROM dbo.zero_ten AS zt;

DECLARE @joydivision NVARCHAR(MAX) = N'';
DECLARE @errors TABLE
(
    id INT PRIMARY KEY IDENTITY,
    error_number INT,
    error_severity INT,
    error_state INT,
    error_message NVARCHAR(MAX)
);

OPEN c;
FETCH NEXT FROM c
INTO @joydivision;

WHILE @@FETCH_STATUS = 0
BEGIN
    BEGIN TRY
        RAISERROR(@joydivision, 0, 1) WITH NOWAIT;
        EXEC sys.sp_executesql @joydivision;
        FETCH NEXT FROM c INTO @joydivision;
    END TRY
    BEGIN CATCH
        INSERT @errors ( error_number, error_severity, error_state, error_message )
        SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE();
        --FETCH NEXT FROM c INTO @joydivision;
    END CATCH;
END;

SELECT *
FROM @errors AS e;

CLOSE c;
DEALLOCATE c;
GO

 

Related Posts

5 thoughts on “Error Handling In SQL Server Cursors”

  1. Hi Erik,

    Long time listener, et cetera. I’ve applied a paradigm that I use for working with cursors to your example and it feels less error prone to me. I’m including the “open c” merely as a landmark – everything prior to that is unchanged.

    WHILE (1=1)
    BEGIN
    FETCH NEXT FROM c INTO @joydivision;
    IF (@@FETCH_STATUS 0)
    BREAK;

    BEGIN TRY
    RAISERROR(@joydivision, 0, 1) WITH NOWAIT;
    EXEC sys.sp_executesql @joydivision;
    END TRY
    BEGIN CATCH
    INSERT @errors ( error_number, error_severity, error_state, error_message )
    SELECT ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE();
    END CATCH;
    END;

    Specifically, there’s only one fetch statement now and if there are no more rows to fetch it terminates the (otherwise) infinite loop.

    Thanks for everything you do.

  2. Just put only one “fetch next ….” statement after the catch block (you’re not limited to put all your code in a try …. catch block)
    (got same hair issue here in The Netherlands)

  3. Surely Isolation would have been a more pertinent song choice at this time 😉

Comments are closed.