Why is my TRY CATCH block in SQL Server immediately exiting on error completely ignoring the remaining code in the block, including my error handling?

Question

Below is a snippet of my TRY/CATCH logic within a stored procedure with error handling (I did not include all of the DECLARE statements). This is being ran within a stored procedure, however I am simply testing this in a session via SSMS (would this affect the behavior?). I did stumble across this thread, but do I have to go to these lengths to simply capture errors?

BEGIN TRY
DECLARE @sql varchar(1000);
SET @sql = 'select 1/0';
EXEC(@sql);
-- On Error the remaining TRY/CATCH below is compeltely ignored I discovered.
DECLARE @error int;
SET @error = @@error;
IF @error > 0
 BEGIN
 SET @raisemessage = 'SQL Backup Error: ' + cast(@rc as varchar(10));
 RAISERROR (@raisemessage, 16,1);
 END
END TRY
BEGIN CATCH
 SET @errormessage = ERROR_MESSAGE();
 IF @errormessage is null
 SET @errormessage = ''
 SET @errorstring = @errorstring + 'Database: ' + @name + ' Error: ' + @errormessage + char(10) + char(10);
END CATCH

Can anyone explain the shortcomings of this TRY/CATCH block? Does this not work in T-SQL?

• mustaccio: Your catch block seems syntactically incorrect and doesn't do anything; how do you know it doesn't run?

Answer 1

This is actually far more straightforward than I thought. Assuming that all of the variables used above are declared elsewhere, the simple answer as to why the catch isn't working is that it is working, but it has syntax errors internally that result in a return value of NULL.

Consider this line:

SET @errorstring = @errorstring + 'Database: ' + @name + ' Error: ' + @errormessage + char(10) + char(10);

Unless you declared @errorstring like this:

DECLARE @errorstring varchar(255) = ''

Or assigned @errorstring as some value, the variable is currently set to NULL. And, by default, concatenating anything to NULL returns... NULL.

Changing the code here to

SET @errorstring = 'Database: ' + @name + ' Error: ' + @errormessage + char(10) + char(10);

Sets the variable to the correct value.

• Eric Swiggum: Yeah thanks... It appears there is no way with a TRY/CATCH block in t-sql to capture both errors of a failed backup ... THROW does, but terminates the loop that my TRY/CATCH block is in... so RAISERROR it is...