2

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

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  
|improve this answer|||||
  • 1
    Your solution is the correct one when you need to catch errors and continue afterward. One cannot catch compile errors in the same scope with TRY/CATCH but the outer proc can catch compile errors in called procs because it's in a different scope. – Dan Guzman Jul 5 '18 at 10:56
  • @DanGuzman I'm guessing it is, but I'm so used to OO programming languages where try..catch scope is at a block level rather than a method(?) level. – e_i_pi Jul 5 '18 at 11:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.