10
\$\begingroup\$

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?

\$\endgroup\$
1
  • 3
    \$\begingroup\$ 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. \$\endgroup\$
    – Hugh Jones
    Oct 7 '11 at 7:55
5
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.