Stored Procedures Template

-- =============================================
-- Author:      <author,,name>
-- Create date: <create date,,="">
-- Description: <description,,>
-- =============================================
CREATE PROCEDURE <SchemaName>.<Prefix>_<ProcedureName>
--Prefix
--usp = UserStoredProcedure or UpdateStoredProcedure;
--isp = InsertStoredProcedure
--dsp = DeleteStoredProcedure
--gsp = GeneralPurposeStoredProcedure
--proc = Prodedure

<@p_param1> <datatype> = <defaultvalue>,
....
....
....
<@p_paramN> <datatype> = <defaultvalue>
 
AS
BEGIN
    SET NOCOUNT ON;
    SET XACT_ABORT,
        QUOTED_IDENTIFIER,
        ARITHABORT,
        ANSI_NULLS,
        ANSI_PADDING,
        ANSI_WARNINGS,
        CONCAT_NULL_YIELDS_NULL ON;
    SET NUMERIC_ROUNDABORT OFF;
  
    DECLARE @ThisTran bit
  
    BEGIN TRY
 
      --Insert, where possibile, CREATE TempTable, INSERT INTO TempTable, SELECT or SET code HERE
 
 
    IF @@TRANCOUNT = 0
    BEGIN
        SET @ThisTran = 1
        BEGIN TRANSACTION ThisTran
    END
  
 
           --Insert transactional code HERE
  
     IF @ThisTran = 1 AND XACT_STATE() = 1
        COMMIT TRAN ThisTran
  
    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 @ThisTran = 1 AND XACT_STATE() <> 0
            ROLLBACK TRAN 
  
        RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
  
    END CATCH
  
END