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.)
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;