Trapping Errors in Stored Procedures

A TRY CATCH block can catch errors in stored procedures called by other stored procedures.  An example is:

BEGIN TRY
    EXEC ParentError
END TRY
BEGIN CATCH
    SELECT Error_Line = ERROR_LINE(),
           Error_Proc = ERROR_PROCEDURE()
END CATCH

Assuming that the ParentError stored procedure calls the ChildError stored procedure which generates an error you'd see output like this:

Error_Line  Error_Proc
----------- -------------
5           ChildError

Error Handling and Transactions

 The TRY CATCH syntax also works well with transactions.  Below is a common pattern used inside stored procedures for transactions.  Any errors cause the transaction to roll back.

BEGIN TRY
    BEGIN TRANSACTION

    INSERT INTO dbo.invoice_header
        (invoice_number, client_number)
    VALUES (2367, 19)

    INSERT INTO dbo.invoice_detail
        (invoice_number, line_number, part_number)
    VALUES (2367, 1, 84367)

    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    IF @@TRANCOUNT() > 0 ROLLBACK TRANSACTION
    -- And do some cool error handling
END CATCH