question

rahulsahay123 avatar image
rahulsahay123 asked

How to peform object specific error handling in stored procedure SQL Server 2008

I have a store procedure ABC. Inside this procedure , i have 10 insert scripts. This procedure is having Try / Catch block. Catch block will give me Error_Message / Error_line / Error_procedure etc information. Scenario: Say 6th Insert statement got failed. So how can i capture the specific table name along with the error message in my error log table ?
sqlserver 2008
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Build your own error message/comment. Before each insert, set your error message to be the name of the table, and then handle it in the catch block as it will be set to the last value. declare @UserComment varchar(max) begin try ... set @UserComment = 'inserting into table5' insert into table5 values (... ... ..) ... set @UserComment = 'inserting into table6' insert into table6 values (... ... ..) ... end try begin catch insert into errortable select errornumber, errormessage, etc., @UserComment end catch
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

rahulsahay123 avatar image rahulsahay123 commented ·
Thanks Kev for the prompt reply. Can you please elaborate the below below statement : " Before each insert, set your error message to be the name of the table, and then handle it in the catch block as it will be set to the last value. " Thanks Rahul Sahay
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
I've updated my answer with an example, but I don't really know how you are handling errors
0 Likes 0 ·
rahulsahay123 avatar image rahulsahay123 commented ·
Thanks Kev...i got the solution.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.