How to trap errors

Once a stored procedure moved beyond simple examples it is very common for them to use transactions to ensure entire operations execute or are rolled back. In order to make error handling in SPs easier you should use the TRY...CATCH syntax.

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
[ { sql_statement | statement_block } ]
END CATCH [ ; ]

Handling Transactions

You can also consider using the @@TRANCOUNT system variable to determine if you have any pending open transactions.

BEGIN TRY
{ sql_statement | statement_block }
END TRY
BEGIN CATCH
    -- Do we have a transaction to rollback?
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END
END CATCH [ ; ]

Raising an error within try catch

RAISERROR can be used within a CATCH statement. This allows you to catch all of the possible errors in a stored proc and raise a single error that you application is expecting, rather than each individual error from the statements themselves.

BEGIN TRY
    -- Execute business logic that might fail
END TRY
BEGIN CATCH
    RAISERROR( 'This function failed with error.', 1, 1);
END CATCH

Example

BEGIN TRY
        BEGIN TRANSACTION;
        -- Execute business logic that might fail

        -- If there is no error we will commit
        COMMIT TRANSACTION;
END TRY
BEGIN CATCH
        -- Rollback if we failed for some reason
        ROLLBACK TRANSACTION;
END CATCH

BEGIN TRY
     -- Execute business logic that might fail
END TRY
BEGIN CATCH
     RAISERROR( 'This function failed with error.', 1, 1);
END CATCH

SQL

WHILE / BREAK / CONTINUE

TRY / CATCH

RAISERROR

Send Feedback