TRY/CATCH in SQL Server

Naomi 7,361 Reputation points
2021-07-08T18:01:53.477+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,851 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,559 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,466 Reputation points
    2021-07-08T18:14:54.817+00:00

    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

    1 person found this answer helpful.
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points MVP
    2021-07-08T21:39:39.21+00:00
    1 person found this answer helpful.