Interview Question of the Week #023 – Error Handling with TRY…CATCH

Error handling with TRY...CATCH comes up often in SQL Server interviews. It separates the work you attempt from the code that responds when an eligible error occurs. One phrase in my old answer needs correction: entering CATCH does not automatically roll back a transaction.

Question: How do you handle errors with TRY...CATCH?

Answer: Put the operation in TRY and the response in the immediately following CATCH. When using an explicit transaction, inspect XACT_STATE() and roll back in the handler if a transaction remains. Run this self-contained example in a fresh query window with no transaction already open. It changes no table:

BEGIN TRY
    BEGIN TRANSACTION;
    THROW 50000, 'Interview demonstration error', 1;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    SELECT ERROR_NUMBER() AS ErrorNumber,
           ERROR_MESSAGE() AS ErrorMessage,
           XACT_STATE() AS TransactionStateAfterRollback;
END CATCH;

The handler returns error number 50000, the demonstration message, and transaction state 0 after the explicit rollback. In real work, use THROW; after cleanup when the caller needs to know the operation failed.

Inside CATCH, ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), and ERROR_PROCEDURE() describe the handled error. They return NULL outside a catch block. TRY...CATCH does not handle every failure; a compile error at the same execution level is one important exception.

Keep TRY and its immediately following CATCH in the same batch; a client batch separator cannot split the pair. For the fuller background, see my earlier explanation of TRY…CATCH.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.