I often write little stored procedures to do this and that. Sometimes they are just for utility to return some diagnostic-level information, sometimes they are horrendously large, sometimes they are for clients to call from their own integrated processes, but usually I am the one to write the calling code in some other application domain like C++, C#, vbScript or whatever.

I also often assist others with translating various business rules into stored procedures, or to give a start so other engineers can finish the job and not be burdened by the nuances of best-practice boilerplate.

I used to write stored procs that were littered with transaction handling. Trancount, do unroll here, don't unroll there, remember what it was upon entry, don't mess it up, etc. just like is done in every MSDN example and many a CodeProject article.

A year or two ago at a Seattle Code Camp or something like that I was enlightened by a presentation (at/by Microsoft) about writing clean stored procedure logic and how nesting all that transactional gobbledyguck really doesn't do anything to help the situation and in almost every case makes things unnecessarily complicated. Apologies to the presenter that I cannot remember.

I'm not saying I don't like transactions... I do like transactions, but as with everything there is a time and a place most appropriate for such constructs, and as a result of that presentation I now believe the best place for transactions is near the highest level in the process, so errors down below will post back up the call chain and cause the top level (or very near it) to decide in one place whether to commit or rollback. In other words, the calling code should handle the transaction lifetime and decision about what to do after an error.

Enter error handling.

Just like in other more, shall we say evolved programming languages, I believe a function should capture an error, add some information to it, and pass it up the chain of command to the next level where that error will be captured, wrapped with more information and passed up again in uniform succession until it reaches the originating caller where the error can be handled and presented, logged or whatever is approptriate. Exception, error, whatever flavor or mechanism you choose, the guiding principle is the same.

So for all you poor Stack Overflowians who are stuck on the couch with your significant other being forced to endure a seemingly endless marathon, nay deathmarch of The Housewives of Orange County, and are still along for the ride, I would appreciate your assistance with the following question:

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 dscription of where my approach falls short and why your version might be a better starting point?

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