Advanced CREATE STORED PROCEDURE Template

When I create a new stored procedure, I tend to use the following code, use it and modify it as much as you like.


CREATE PROC usp_test
/** -----------------------------------------------------------------------------------------------
***
*** Database naming and coding conventions
*** S:\IT\Databases\DRL-IT-SQLServerCodingStandards.docx
***
*** Description :
***                    
***

Example Execution :

    DECLARE @Success INT, @ErrProc nvarchar(4000),  @ErrNum INT, @ErrMsg nvarchar(4000),@ErrSeverity INT,@ErrState INT,@ErrLine INT, @ErrStartDate DATETIME,@ErrEndDate DATETIME
  
       EXEC usp_test
   
    @Success = @Success OUTPUT,
    @ErrProc = @ErrProc OUTPUT,
    @ErrNum = @ErrNum OUTPUT,
    @ErrMsg = @ErrMsg OUTPUT,
    @ErrSeverity = @ErrSeverity OUTPUT,
    @ErrState = @ErrState OUTPUT,
    @ErrLine = @ErrLine OUTPUT,
    @ErrStartDate = @ErrStartDate OUTPUT,
    @ErrEndDate = @ErrEndDate OUTPUT

    select @Success, @ErrStartDate


*** Revision History: 
*** v1.0            Gary Cowan             09 Sep 2000        - Created Stored Procedure
***
** -------------------------------- External Variable Declaration(s) -----------------------------*/






 ----------------------------------- Error Handling Declaration------------------------------------------------

   @Success INT OUTPUT,
   @ErrProc nvarchar(4000) OUTPUT,
   @ErrNum INT OUTPUT,
   @ErrMsg nvarchar(4000) OUTPUT,
   @ErrSeverity INT OUTPUT,
   @ErrState INT OUTPUT,
   @ErrLine INT OUTPUT,
   @ErrStartDate DATETIME OUTPUT,
   @ErrEndDate DATETIME OUTPUT

---------------------------------------------------------------------------------------------------
AS

/* Internal Variable Declaration(s) and Initialation(s)                                           */





/* ---------------------------------------------------------------------------------------------- */

 SET NOCOUNT ON
 SET XACT_ABORT ON
 SET TRANSACTION ISOLATION LEVEL READ COMMITTED


 SELECT @ErrStartDate = GETDATE()

 BEGIN TRY
            BEGIN TRAN
            -- Comment: blah blah blah

         


            SELECT 1/1









            IF @@TRANCOUNT > 0
            COMMIT TRAN
            SELECT @Success = 1, @ErrEndDate = GETDATE()
 END TRY

 BEGIN CATCH

            IF @@TRANCOUNT > 0

            ROLLBACK  
              -- Raise an error with the details of the exception

    

            SELECT     
            @ErrMsg = ERROR_MESSAGE(),
            @ErrSeverity = ERROR_SEVERITY(),
            @ErrState = ERROR_STATE(),
            @ErrEndDate = GETDATE(),
            @ErrProc = ERROR_PROCEDURE(),
            @ErrLine = ERROR_LINE(),
            @ErrNum = ERROR_NUMBER()


            SELECT @Success = 0
            RAISERROR(@ErrMsg, @ErrSeverity, @ErrState)

 END CATCH