Sql-server – SQL Server TRY..CATCH resume on terminating error

error handlingsql serverstored-procedures

I'm trying to get a master stored procedure to execute through to completion, where the master SP calls child SPs to perform modular tasks that are independent of each other (i.e. – if one fails, that shouldn't halt execution of the others). One of these child SPs might fail, and I don't know in advance why it might fail, but one scenario I have encountered is that it's trying to select from a table that does not exist. I would like that error logged, and then for the master script to continue. I'm guessing that selecting from a non-existent table is a terminal error, and I want to know if there is a way to continue even on terminal errors.

(Yes, I know that I can interrogate the information schema to determine table existence, but I'm looking to catch errors that haven't been defensively coded against yet – today it could be a missing table, tomorrow it could be something else we haven't considered that brings the master script to a screaming halt.)

Example code:

DROP TABLE IF EXISTS my_error_log;
CREATE TABLE my_error_log (
    error_time DATETIME,
    error_msg VARCHAR(MAX)
);
GO

CREATE OR ALTER PROCEDURE failProc
AS
BEGIN
    BEGIN TRY
        -- This one fails, is caught, and continues...
        -- SELECT 1/0;
        -- This one fails, is caught, and terminates the master script...
        SELECT * FROM non_existent_table;
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log
        SELECT CURRENT_TIMESTAMP, ERROR_MESSAGE();
    END CATCH
END;
GO  

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        EXEC failProc;
        EXEC failProc;
        EXEC failProc;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO  

EXEC masterFailProc
GO

SELECT * FROM my_error_log;
GO

DROP PROCEDURE masterFailProc;
DROP PROCEDURE failProc;
DROP TABLE my_error_log;

Best Answer

I've found a solution, not as elegant as I'd hoped, but it works. If someone has something better I'd like to hear it.

The problem with the masterFailProc is it's wrapping a TRY..CATCH block around the entire script, instead of around individual EXEC calls. If the definition is changed as below, then it will happily log errors on a per-exec basis rather than terminating the entire script:

CREATE OR ALTER PROCEDURE masterFailProc
AS
BEGIN
    SET XACT_ABORT OFF;
    BEGIN TRY
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'start';
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        BEGIN TRY EXEC failProc; END TRY BEGIN CATCH /* log error */ END CATCH;
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'end';
    END TRY
    BEGIN CATCH
        INSERT INTO my_error_log SELECT CURRENT_TIMESTAMP, 'fatal error';
    END CATCH
END;
GO
Related Question