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 ?
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