SQL Server Exceptions Working

Estimated study time: 10 minutes. Catch errors before they crash your batch.

SQL Server handles runtime errors using a TRY...CATCH block, similar in spirit to exception handling in most programming languages. Any error inside the TRY block transfers control to the matching CATCH block instead of stopping the batch.

Basic TRY...CATCH Structure

BEGIN TRY
    SELECT 1 / 0;  -- divide-by-zero error
END TRY
BEGIN CATCH
    PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

Error Information Functions

Inside a CATCH block, SQL Server exposes several functions describing exactly what went wrong:

• ERROR_NUMBER() - the internal error number
• ERROR_MESSAGE() - the descriptive error text
• ERROR_SEVERITY() - how serious the error is
• ERROR_STATE() - a state number for the error
• ERROR_LINE() - the line number where the error occurred
• ERROR_PROCEDURE() - the name of the procedure or trigger, if any

BEGIN TRY
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER()    AS ErrorNumber,
        ERROR_MESSAGE()   AS ErrorMessage,
        ERROR_SEVERITY()  AS ErrorSeverity,
        ERROR_LINE()      AS ErrorLine;
END CATCH;

Raising Custom Errors: THROW vs RAISERROR

THROW is the modern, simpler way to raise an error and re-throw the original error caught in a CATCH block.

BEGIN TRY
    UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
    IF @@ROWCOUNT = 0
        THROW 51000, 'Account not found.', 1;
END TRY
BEGIN CATCH
    THROW;  -- re-throws the original error to the caller
END CATCH;

RAISERROR is the older syntax, still useful when you need formatted messages or specific severity levels.

RAISERROR('Balance cannot go below zero for account %d.', 16, 1, @AccountId);

Using Transactions with TRY...CATCH

Wrapping a transaction inside TRY...CATCH lets you roll back cleanly the moment something fails, keeping the database consistent.

BEGIN TRY
    BEGIN TRANSACTION;
        UPDATE Accounts SET Balance = Balance - 500 WHERE AccountId = 1;
        UPDATE Accounts SET Balance = Balance + 500 WHERE AccountId = 2;
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    PRINT 'Transaction rolled back: ' + ERROR_MESSAGE();
END CATCH;