Inserting error data into a table from CATCH statement within a TRY CATCH

  • I've been researching for past two days and hitting my head against wall, but I can't figure this out.

    I am trying to insert error message into a table along with the data that goes with it. So here how my statement looks:

    BEGIN CATCH

    --INSERT INTO InsertStatementErrors

    SELECT f.* , 0 AS [ErrorResolvedYesNo],

    ERROR_MESSAGE() [error_msg],

    ERROR_NUMBER() ErrorNBR ,

    ERROR_SEVERITY() Severity ,

    ERROR_LINE() ErrorLine ,

    GETDATE() AS [TimeAndDateAdded]

    FROM froi_import f

    WHERE Insured_ID = @Insured_ID and record_id = @record_id

    ROLLBACK TRANSACTION

    END CATCH;

    When I comment out the INSERT INTO, it displayed the error on the screen. But when I enable the INSERT INTO, it doesn't insert the data into the table neither it gives me any error message. I can't figure out what I am doing wrong.

    ------------
    🙂

  • You are rolling back the transaction after you insert the errors so that is why they are displayed, but not inserted. You need to insert the errors after you rollback. Of course you will probably lose the data in your table.



    Microsoft Certified Master - SQL Server 2008
    Follow me on twitter: @keith_tate

    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • Keith, Thanks. That did the Trick. Here is the correct Code:

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    INSERT INTO rcmd.FROI_InsertStatementErrors

    SELECT f.* , 0 AS [ErrorResolvedYesNo],

    ERROR_MESSAGE() [error_msg],

    ERROR_NUMBER() ErrorNBR ,

    ERROR_SEVERITY() Severity ,

    ERROR_LINE() ErrorLine ,

    GETDATE() AS [TimeAndDateAdded]

    FROM rcmd.froi_import f

    WHERE Insured_ID = @Insured_ID and record_id = @record_id

    END CATCH;

    The Variable Idea sound good. I'll try it next time.

    ------------
    🙂

  • //Ravi (11/12/2013)


    Keith, Thanks. That did the Trick. Here is the correct Code:

    END TRY

    BEGIN CATCH

    ROLLBACK TRANSACTION

    INSERT INTO rcmd.FROI_InsertStatementErrors

    SELECT f.* , 0 AS [ErrorResolvedYesNo],

    ERROR_MESSAGE() [error_msg],

    ERROR_NUMBER() ErrorNBR ,

    ERROR_SEVERITY() Severity ,

    ERROR_LINE() ErrorLine ,

    GETDATE() AS [TimeAndDateAdded]

    FROM rcmd.froi_import f

    WHERE Insured_ID = @Insured_ID and record_id = @record_id

    END CATCH;

    The Variable Idea sound good. I'll try it next time.

    I'm not a big fan of the way you've constructed your INSERT/SELECT statements on your CATCH logic. I prefer to specify the columns in both the INSERT statement and the SELECT statement. I say this only because if for some reason rcmd.froi_import were ever to change your catch logic would fail as well. Just saying.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • Kurt W. Zimmerman (11/12/2013)


    I'm not a big fan of the way you've constructed your INSERT/SELECT statements on your CATCH logic. I prefer to specify the columns in both the INSERT statement and the SELECT statement. I say this only because if for some reason rcmd.froi_import were ever to change your catch logic would fail as well. Just saying.

    +1000 to that.

    I also have to say that in general the code makes this look a looping/cursor type of insert. :w00t:

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Kurt, I agree with you and I don't like the way it is. But it's one of those things that I users need it "Immediately" and I didn't have time to put 70+ columns in the code. I'll take care of it in version 2 :-).

    Sean, yes it is Looping/cursor type of insert. I hate using cursors, but the application, into which the data is being inserted in, can't handle set based inserts. It has to be one by one.

    ------------
    🙂

  • //Ravi (11/14/2013)

    Sean, yes it is Looping/cursor type of insert. I hate using cursors, but the application, into which the data is being inserted in, can't handle set based inserts. It has to be one by one.

    I will take you word for it but...we don't insert data into applications, we insert data into a database (which can handle multiple row inserts).

    At any rate, glad you were able to sort your issue.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Hey Ravi,

    Can you please share your full code , I am trying to get the error data in catch block but some how using while loop its getting into infinite loop.

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

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