This template takes several things into consideration:

  • Documentation
  • Error handling and -logging
  • Transaction handling

Over the time we developed a proven design to write fail-safe Stored Procedures for SQL-Server 2012 and above that serve our client’s needs.

We would like to give credits to Erland Sommerskog for writing an excellent article regarding Error and Transaction Handling in SQL Server that helped us implementing this template.

USE [#DatabaseName#]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ===========================================================================
-- company/project:  RAYCOON.com GmbH / Blog for T-SQL programming
-- created:          #YYYY/MM/DD# by #author#
-- used by module:   backend service XYZ
-- description:      functionality of this code
------------------------------------------------------------------------------
-- INPUT parameter:
--
------------------------------------------------------------------------------
-- OUTPUT parameter:
--
------------------------------------------------------------------------------
-- possible optimizations (for later implementation):
-- 
------------------------------------------------------------------------------
-- logic or SP's that might need to be altered if this SP is being changed:
-- 
-- =========================================================
-- CHANGE HISTORY:
------------------------------------------------------------
-- changed: YYYY/MM/DD by Your Name
-- description: brief description of changes
------------------------------------------------------------
-- changed: 
-- description: 
-- =========================================================
CREATE OR ALTER PROCEDURE [#StoredProcedureSchema#].[#StoredProcedureName#]
(
	 @Variable1 INT
	,@Variable2 INT
	,@Variable3 INT OUTPUT
)
AS
SET XACT_ABORT ON;
SET NOCOUNT ON;

BEGIN TRY
-- ###########################################################################

	-- Your business-logic outside of the transaction
	
	BEGIN TRANSACTION;
	
	  -- Put your transactional business-logic in here
	
	COMMIT TRANSACTION;

-- ###########################################################################
END TRY
BEGIN CATCH

	IF @@trancount > 0
	  ROLLBACK TRANSACTION;

	-- Print error information.
	--EXECUTE WriteErrorToLog;

	;THROW;

END CATCH; -- Try Catch

-- ###########################################################################
-- EXAMPLE CALL
/*
EXECUTE [#StoredProcedureSchema#].[#StoredProcedureName#]
	@Variable1 = 1,
	@Variable2 = 2
*/
-- ###########################################################################
GO