Error Handling in Nested Procedures and Logging Custom Errors

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    Comments posted to this topic are about the item Error Handling in Nested Procedures and Logging Custom Errors

  • mikeg13

    SSCommitted

    Points: 1851

    How can I see the contents of the TXT files? When I click on the link to the TXT files, I get a SQL Server Central page with the contents saying that it could not find the file. This is the case for both TXT files. I am curious to see what code you added, since I am working on this type of problem now.

    Mike

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    i did sent email to steve jones

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 720939

    All the files are at the bottom of the article. I didn't notice the links internally, which needed to be removed.

  • voutmaster

    Mr or Mrs. 500

    Points: 517

    I noticed that the author uses this statement to grab the newly inserted identity:

    set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG');

    I think he meant to specify the table 'ERROR_LOG', not 'HIST_ERROR_LOG'.

    Also, IDENT_CURRENT will return the identity value of the table provided across all sessions, meaning you could get a value that some other thread generated. I usually rely on scope_identity(), which works all the time, except if you have a trigger on the table in which you're inserting into and that trigger happens to be inserting into yet another table with an identity (not a common scenario).

    Another confusing point of the article is the author's variable naming conventions, prefix of: '@$'. You only need to use @ to indicate something's a variable. I couldn't find any reference as to what the $ would stand for. My guess it's a remnant of an older style when variables were prefixed with $ (e.g. environment variables in a shell).

  • Leo Peysakhovich

    Hall of Fame

    Points: 3880

    pvoutov (7/7/2008)


    I noticed that the author uses this statement to grab the newly inserted identity:

    set @INSERTED_IDENTITY_ID = IDENT_CURRENT('HIST_ERROR_LOG');

    I think he meant to specify the table 'ERROR_LOG', not 'HIST_ERROR_LOG'. (Correct)

    Also, IDENT_CURRENT will return the identity value of the table provided across all sessions, meaning you could get a value that some other thread generated. I usually rely on scope_identity(), which works all the time, except if you have a trigger on the table in which you're inserting into and that trigger happens to be inserting into yet another table with an identity (not a common scenario).

    Another confusing point of the article is the author's variable naming conventions, prefix of: '@$'. You only need to use @ to indicate something's a variable. I couldn't find any reference as to what the $ would stand for. My guess it's a remnant of an older style when variables were prefixed with $ (e.g. environment variables in a shell).

    You are right about Error_Log table name.

    $ is to distinguish templated variables from lately declared by developer's variables

  • Dan Guzman - Not the MVP

    Hall of Fame

    Points: 3755

    Thanks Leo for the article, the information was very interesting. Your English is pretty good, though there were plenty of grammar errors. I would have thought your editor would have caught them. Keep practicing. I look forward to your next article.

  • Anipaul

    SSC-Insane

    Points: 24681

    Nice article. You have really encouraged lot of people to write. We may see lot of articles from new bies.....:)

  • joshcsmith13

    Hall of Fame

    Points: 3381

    Thanks for the article Leo. It seems well suited to function in the environment you propose. I want to know why I never see anybody use the SAVE TRAN statement. In my research and testing, this is the only way to isolate a transaction within a stored procedure, so that it can be independently rolled back without regards to the transaction state before the procedure was called. ?? Has anybody else used SAVE TRAN?

  • ballan

    SSC Enthusiast

    Points: 113

    Pity there are no autonomous transactions in SQL Server. Has anyone used a CLR to accomplish autonomous transactions?

  • naveenreddy.84

    Ten Centuries

    Points: 1252

    Hi All,

    My Nested code is similar to the below code template

    CREATE PROCEDURE usp_Proc1

    AS

    BEGIN TRY

    BEGIN TRAN TRAN1

    EXEC usp_Proc2

    COMMIT TRAN TRAN1

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN TRAN1

    END CATCH

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

    CREATE PROCEDURE usp_Proc2

    AS

    BEGIN TRY

    BEGIN TRAN TRAN2

    EXEC usp_Proc3

    COMMIT TRAN TRAN2

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN TRAN2

    END CATCH

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

    CREATE PROCEDURE usp_Proc3

    AS

    BEGIN TRY

    BEGIN TRAN TRAN3

    SOURCE CODE ----

    COMMIT TRAN TRAN3

    END TRY

    BEGIN CATCH

    ROLLBACK TRAN TRAN3

    END CATCH

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

    In summary, usp_Proc1 calls usp_Proc2 which in turn calls usp_Proc3.

    I need to log all the error that might occur in above flow and also the source(proc_name) of error with description.

    Regards,

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply