Post reply

Table variable based error logging inside Transactions

  • March 19, 2012 at 9:29 pm

    #258089

    Comments posted to this topic are about the item Table variable based error logging inside Transactions

  • March 20, 2012 at 10:10 am

    #1461745

    Is it really a necessity to use a table variable?

    This is shorter with the same result:

    CREATE TABLE AppleTest

    ( testint INT,

    inserted_time DATETIME2

    )

    --Error logging table

    CREATE TABLE Errorlog

    ( errordetails NVARCHAR(MAX),

    loggedtime DATETIME2

    )

    --Starting the transaction

    BEGIN TRAN

    BEGIN

    --Beginning the try block which has one correct line and then a divide by zero exception causing line

    BEGIN TRY

    INSERT INTO AppleTest

    SELECT 5/5,GETDATE() --This is valid

    INSERT INTO AppleTest

    SELECT 1/0,GETDATE() --Error line as divide by zero will cause an error

    INSERT INTO AppleTest

    SELECT 10/2,GETDATE()-- Valid ;This is unreachable as control goes to catch after encountering the error

    COMMIT TRAN -- This is unreachable as control goes to catch after encountering the error

    END TRY

    --Beginning the catch block which has insertion to a errolog TABLE VARIABLE and then ROLLBACK

    BEGIN CATCH

    ROLLBACK TRAN -- This will rollback the transaction and the valid insert Select 5/5,getdate() will now be rolled back.But the insertion in the TABLE VARIABLE will not be rolled back

    INSERT INTO Errorlog

    SELECT ERROR_MESSAGE(),GETDATE() --error_message() returns the error message,getdate() to store the time at which the error occured

    END CATCH

    END

    --Now check the output in the physical error table

    SELECT * FROM Errorlog

    --Now check the output in the physical table

    SELECT * FROM AppleTest

  • March 20, 2012 at 11:18 am

    #1461792

    Thanks for the reply.

    Table variables can be handy if we want to capture more info other the error message, like some record which got failed, the user who tried to insert that record . This was a pretty simple demo example. Consider a case where some user was doing some insert which contained this error, we can capture the record he was trying to insert, info about him, i mean more specific details about the error.

  • May 11, 2016 at 6:59 am

    #1878163

    Thanks for the script.

Viewing 4 posts - 1 through 3 (of 3 total)

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