Skip to main content

SQL Server Stored Procedure template.

-- -----------------------------------------------------------------------------
-- Stored Procedure code template
-- https://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/
--
-- Do you use code templates in SSMS? I am sure that at least once you happened
-- to click "New stored procedure" in the object explorer context menu.
--
-- The default template for this action is a bit disappointing and the only
-- valuable line is "SET NOCOUNT ON". The rest of the code has to be heavily
-- rewritten or deleted. Even if you use the handy keyboard shortcut for
-- "Specify values for template parameters"  (CTRL+SHIFT+M), you end up entering
-- a lot of useless values. For instance, I find it very annoying having to
-- enter stored procedure parameters definitions separately for name, type and
-- default value.
--
-- Moreover, one of the questions I see asked over and over in the forums at
-- SqlServerCentral is how to handle transactions and errors in a stored
-- procedure, something that the default template does not.
--
-- Long story short, I'm not very happy with the built-in template, so I decided
-- to code my own:
-- -----------------------------------------------------------------------------
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
create procedure <procedurename, sysname, >
as
begin
    set nocount on;
    set xact_abort,
        quoted_identifier,
        ansi_nulls,
        ansi_padding,
        ansi_warnings,
        arithabort,
        concat_null_yields_null on;
    set numeric_roundabort off;

    declare @localTran bit;

    if @@TRANCOUNT = 0
        begin
            set @localTran = 1;
            begin transaction LocalTran;
        end;

    begin try
        --
        -- Insert code here...
        --
        if @localTran = 1 and XACT_STATE() = 1
            begin
                commit tran LocalTran
            end;
    end try
    begin catch
        declare @ErrorMessage nvarchar(4000);
        declare @ErrorSeverity int;
        declare @ErrorState int;

        select @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();

        if @localTran = 1 and XACT_STATE() <> 0
            begin
                rollback tran
            end;

        raiserror(@ErrorMessage, @ErrorSeverity, @ErrorState);
    end catch;
end;