0

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?

  • Your catch block seems syntactically incorrect and doesn't do anything; how do you know it doesn't run? – mustaccio Jun 20 at 22:25
3

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.

  • 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... – Eric Swiggum Jun 21 at 16:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.