This post is part of a series of posts on exception handling in SQL Server. This post includes a template listing below. There is a more advanced template listing in Part 9 that creates a new database, tables for that database and a series of stored procedures. That listing is then improved upon and modified again in the posts that follow it. What is added is user-defined errors with RAISERROR inside a TRY…CATCH block. See Part 13 for our final part in this series on SQL Server Exception Handling.

A Simplified Template

Below is a template, or “boilerplate” code for exception handling in SQL Server from the website sqlhints.com. This template is the forth part of a four-part series of articles on exception handling.

This template below example has spawned a series of posts on exception handling in SQL Server. What is the best way to do this? There is another template in this series that is in the post called SQL Server Exception Handling Part 7 that shows the MSDN suggested way of handling transaction that maybe the best way. Also, in Part 9 there is an entire listing that creates a new database, tables and stored procedures. You can Execute the listing, then run the AllProcedures stored procedure to test it.

Throughout the series of posts on exception handling in SQL Server, we discuss TRY…CATCH, TRANSACTION and RAISERROR.

Sqlhints.com Template

CREATE PROCEDURE dbo.ErrorHandlingTemplate
AS
BEGIN
    BEGIN TRY
        SET NOCOUNT ON
        SET XACT_ABORT ON
 
        --  Code Which Doesn't Require Transaction
 
        BEGIN TRANSACTION
            -- Code which Requires Transaction 
        COMMIT TRANSACTION
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 AND XACT_STATE() <> 0 
            ROLLBACK TRAN
 
        -- Do the Necessary Error logging if required
        -- Take Corrective Action if Required
 
        THROW --RETHROW the ERROR
    END CATCH
END

Hey! We hit a milestone here at begincodingnow.com. This is our 500th post.