locked
ERROR LOGGING IN STORED PROCEDURES RRS feed

  • Question

  • Hello,
    I need to build a error logging framework to have stepwise log.
    We are using SQL server 2008 R2.The requirements are to have detailed error logging in the stored procedures based on an input flag.

    1) If the stored procedure has 4 update statements and the last one failed, I need to log to an error table the details on the ones successfully updated and where the error occured.

    2) And I also need to log the Stored Procedure call itself (as it was called with the argument values). Is there a way to do this from within the SP itself. A generic piece of code that I can use in any SP.


    I have template for error handling in stored procedure. I need to include the above 2 requirements.
    I am looking for ideas/ suggestions on how best to implement this.

    - BGOP

     


    -------------------------------------------------------------------------------------------------------------

    CREATE PROCEDURE [dbo].[TEMPLATE]
    AS
    BEGIN

     SET NOCOUNT ON
      DECLARE    
       @ErrorMessage VARCHAR(4000), 
       @Source VARCHAR(100),
       @ErrorNumber INT,
       @ErrorSeverity INT,
       @ErrorState INT,
       @ErrorLine INT,

     
     
     SET @Source =OBJECT_NAME(@@PROCID)

     -- CHECK BUSINESS RULES AGAINST VIOLATIONS  
     
        IF <CHECK RULE1>
        BEGIN
      RAISERROR (50001,
            16, -- Severity.  
            1   -- State. 
            );
      RETURN -1  
     END
     
     IF <CHECK RULE2>
     BEGIN
      RAISERROR (50002,  
            16, -- Severity.  
            1, -- State.
            @Variable1, -- variable to substitute in error message
            @Variable2  -- variable to substitute in error message
            );     
      RETURN -1  
     END

     BEGIN TRAN;
      
     
     BEGIN TRY

      SELECT 'insert code here'
      COMMIT
     END TRY
     
     BEGIN CATCH
      SET @LogDesc = ERROR_MESSAGE()
      SET @ErrorNumber = ERROR_NUMBER()
      SET @ErrorSeverity = ERROR_SEVERITY()
      SET @ErrorState = ERROR_STATE()
      SET @ErrorLine = ERROR_LINE()
      
      IF @@TRANCOUNT > 0
       ROLLBACK TRANSACTION
       
      -- The SP below, inserts to the errorlog table and does a RAISERROR
      EXEC LogErrorToTable  @ErrorMessage, @Source, @ErrorNumber, @ErrorSeverity,  @ErrorState , @ErrorLine
      
      RETURN -1
     END CATCH
     
        RETURN 0
    END
    GO

    Friday, December 6, 2013 7:13 PM

Answers

  • 1) If the stored procedure has 4 update statements and the last one failed, I need to log to an error table the details on the ones successfully updated and where the error occured.

    Well, I would expect a transaction, so that if one UPDATE fails, the transaction is rolled back.

    2) And I also need to log the Stored Procedure call itself (as it was called with the argument values). Is there a way to do this from within the SP itself. A generic piece of code that I can use in any SP.


    Nope, no such thing. You would have to hand-write code for about every stored procedure, which obviously is error-prone and not to be relied on.

    But look at SQL Server Audit. (Which requires Enterprise Edition). I believe that you can audit stored procedure calls, but I don't want to bet on that it caputres parameter values.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 6, 2013 11:21 PM
  • Take a look at Extended  Events as well.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 8, 2013 10:10 AM
    Answerer

All replies

  • 1) If the stored procedure has 4 update statements and the last one failed, I need to log to an error table the details on the ones successfully updated and where the error occured.

    Well, I would expect a transaction, so that if one UPDATE fails, the transaction is rolled back.

    2) And I also need to log the Stored Procedure call itself (as it was called with the argument values). Is there a way to do this from within the SP itself. A generic piece of code that I can use in any SP.


    Nope, no such thing. You would have to hand-write code for about every stored procedure, which obviously is error-prone and not to be relied on.

    But look at SQL Server Audit. (Which requires Enterprise Edition). I believe that you can audit stored procedure calls, but I don't want to bet on that it caputres parameter values.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Friday, December 6, 2013 11:21 PM
  • Take a look at Extended  Events as well.

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, December 8, 2013 10:10 AM
    Answerer
  • Thank you
    Monday, December 9, 2013 1:59 PM
  • Thank you, Will take a look at Extended  Events.
    Monday, December 9, 2013 2:00 PM