try/catch workaround for SQL Server 2000

In SQL Server 2005 Microsoft introduced try/catch functionality. It’s a necessity when you are working with resources that are outside the sql servers control. This might be linked databases where the connection and/or schema of the linked server and database might fall out or be of the wrong version so perfectly legal statements all of a sudden becomes illegal. With try/catch you are able to catch and handle those situations like notify an operator that a database on a remote server is not available.

I had this problem in SQL Server 2000 and I finally found a way to work around those batch-breaking errors and allow me to log that there was an error and continue with the batch.

Here is an example how errors break the batch. You will never see “Done!” printed as the select causes an error that breaks batch.

print ‘Invalid select that breaks batch (Done! will never be printer)’;
select * from atablethatdoesnotexist;
print ‘Done!’;

The workaround is to execute the statements that might fails as a separate batch and one way to do it is by using sp_executesql. This example does exactly the same as above but with a possibility to catch the error and without breaking the batch.

print ‘Invalid select that does not break the batch’;
declare @rc int;
declare @sql nvarchar(500);
set @sql = N‘select * from atablethatdoesnotexist;’;
exec @rc = sp_executesql @sql;
if @rc <> 0 print ‘An error occured but the batch continued!’;
print ‘Done!’;

This is just a workaround, if you can I would recommend to upgrade to SQL Server 2005 instead.

Leave a Reply

Your email address will not be published. Required fields are marked *