TRY/CATCH in SQL Server

Question

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.

Answer 1

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

Answer 2

What Tom said. In addition vote here to stop this madness: http://feedback.azure.com/forums/908035-sql-server/suggestions/33673684-try-catch-should-always-work