# SQL Server stored procedure boilerplate

What would you do to improve upon this boilerplate empty stored procedure, being mindful of the delicate balance between length, complexity, performance and clarity?

-- =============================================
-- Author:      The usual suspects
-- Create date: 10/06/2011
-- Description:
--
-- Nice long description about the procedure
--
-- =============================================
CREATE PROCEDURE

[dbo].[My_Stored_Proc]
(
-- exampleParam is an example parameter.
@exampleParam INT = 30
)
AS
BEGIN   -- main
SET NOCOUNT ON
BEGIN TRY
DECLARE @crlf varchar(2)
SET @crlf = CHAR(13) + CHAR(10)

-- *** DO YOUR STUFF HERE ***

END TRY
BEGIN CATCH
-- Error handler
DECLARE @ErrorNumber INT
DECLARE @ErrorSeverity INT
DECLARE @ErrorState INT
DECLARE @ErrorProcedure NVARCHAR(4000)
DECLARE @ErrorLine INT
DECLARE @ErrorMessage NVARCHAR(4000)

DECLARE @ErrorDescription NVARCHAR(4000)

-- retrieve error info
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorProcedure = ERROR_PROCEDURE(),
@ErrorLine = ERROR_LINE(),
@ErrorMessage = ERROR_MESSAGE();

-- build custom error description
SELECT @ErrorDescription = @crlf + @crlf + 'Base Error:\t[' + CAST(@ErrorNumber AS VARCHAR) + '] ' +
@ErrorMessage + @crlf + @crlf +
'exampleParam:\t' + CAST(@exampleParam AS VARCHAR) + @crlf +
'Application:\t' + APP_NAME() + @crlf +
'User:\t' + SYSTEM_USER + @crlf +
'Database:\t' + DB_NAME() + @crlf +
'Procedure:\t' + @ErrorProcedure + @crlf +
'Line:\t' + CAST(@ErrorLine AS VARCHAR) + @crlf +
'Severity:\t' + CAST(@ErrorSeverity AS VARCHAR) + @crlf +
'State:\t' + CAST(@ErrorState AS VARCHAR);

RAISERROR(@ErrorDescription, @ErrorSeverity, 1)
RETURN @@ERROR
END CATCH
END     -- main


For instance, is there a nice way to move that error handler out of the stored proc so the logic can be shared and not duplicated inside each procedure? Notice it can be nice to include the values of the parameters in that error message (exampleParam above).

Do you agree or disagree with my stance on handling transaction rollbacks in stored procs? (lack thereof)

Do you have or can you write an example of better boilerplate along with a description of where my approach falls short and why your version might be a better starting point

What about SET XACT_ABORT {ON | OFF}? Which option for XACT_ABORT would be a best practice?

• I totally agree with the suggestion not to fiddle about with transaction logic in stored procedures. The main reason for this is that if you write a procedure to be agnostic about its calling routine then it becomes much simpler to reuse it. Generally I aim to have one exception handler at the top level - and usually in the work I do this turns out to be client-side code - and this top level routine is resposible for the commit/rollback. I would say your boilerplate is pretty good, but if you apply it to every sp and function you risk overkill.
– Hugh Jones
Oct 7, 2011 at 7:55

All in all it’s a good idea. Have you considered moving your crlf and CATCH logic; or a portion of it; to a reusable function? This would help ensure; wherever it’s used; that it remain consistent and you don’t have the same code all over the place.