Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
sp_Template.sql #blog
-- http://spaghettidba.com/2011/07/08/my-stored-procedure-code-template/
-- =============================================
-- 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
COMMIT TRAN LocalTran
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
ROLLBACK TRAN
RAISERROR ( @ErrorMessage, @ErrorSeverity, @ErrorState)
END CATCH
END
@ali768

This comment has been minimized.

Copy link

commented Oct 6, 2017

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
        ROLLBACK TRAN
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.