question

NaomiNNN avatar image
0 Votes"
NaomiNNN asked NaomiNNN commented

TRY/CATCH in SQL Server

Hi everybody,

I just quickly reviewed Erland's article the section about TRY/CATCH, but I still want to understand something as we didn't get error logged properly.

Our procedures have at the very top of each one

SET NOCOUNT, XACT_ABORT ON;

Then they all have code such as

try

 code here
 return 0

end try
BEGIN CATCH

         IF @@TRANCOUNT > 0
             ROLLBACK TRANSACTION;

         SELECT
             @Check_Error = ERROR_NUMBER()
           , @Check_Error_Message = ERROR_MESSAGE();

         SET @out_Status_txt
             = '**ERROR** in ' + @usp_name + ' Error Message: ' + @Check_Error_Message + ' Error code: '
               + CONVERT(VARCHAR(25), @Check_Error);

         EXECUTE dbo.usp_write_audit
             @inp_process_txt = @inp_process_txt
           , @inp_sub_process_txt = @sub_process_txt
           , @inp_type_id = 2
           , @inp_action_txt = @out_Status_txt
           , @inp_load_file_detail_id = 0
           , @inp_job_nbr = @inp_load_report_job_nbr;

         SET @out_Status_nbr = 2;
         RETURN 2;

     END CATCH;


One of the procedures had an error in the MERGE command (the code used wrong alias which was not used and it was giving error in run-time about unbound column). However, there is no audit information about error in that procedure, only the caller's procedure somehow trapped it.

Do you see why we didn't end up with the log for the error?

Thanks in advance.



sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
1 Vote"
TomCooper-6989 answered

TRY/CATCH does not catch compilation errors in the procedure which contains the compilation error. That has to be caught in the code that calls the procedure that has the compilation error. Erland has an example of this in https://www.sommarskog.se/error_handling/Part1.html - it is near the end of the TRY-CATCH section 2.1.

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered NaomiNNN commented
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Too bad that there is only 1 permitted answer per thread. I'll try to vote next time, right now it's prompting me again for my account's info.

0 Votes 0 ·