SQL Server stored procedure boilerplate

Question

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?

Answer 1

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.