Jerry Nixon on Windows: The new way to handle errors in SQL 2005 tSQL (using TRY)

Jerry Nixon on Windows

Friday, March 14, 2008

The new way to handle errors in SQL 2005 tSQL (using TRY)

The syntax is so straight-forward, you might think Microsoft outsourced the work. Here you are:

/*
Error Handling
*/

-- old school, could not prevent exception display

select 4/0
if (@@error = 8134)
print 'Error Encountered'
print 'Execution Continues'

-- try catch

begin try
select 4/0
print 'Execution Halts'
end try
begin catch
print @@error
print error_message()
print ' error caught'
end catch

-- compile error

BEGIN TRY
PRINT 'Inside Try-Block'
SELECT ** FROM T /* will cause syntax error */
END TRY
BEGIN CATCH
print @@error
print error_message()
print ' error caught'
END CATCH

-- "lower" error (compile not until execution)

BEGIN TRY
PRINT 'Inside Try-Block'
EXEC ('SELECT ** FROM T ') /* compile error in the lower scope */
END TRY
BEGIN CATCH
print @@error
print error_message()
print ' error caught'
END CATCH