Error Handling with Try/Catch

Catching Errors AFTER they happen

Exceptions

When software programs or stored procedures try to do something that they shouldn’t - like divide by zero, access a file that isn’t there, or write to a write-protected sector - it will throw an exception.

Unhandled exceptions will cause the program to error out, or the stored procedure to fail. (Sometimes this is what we want).

Other times, however, we what to catch the exceptions before it causes or program (or stored procedure) to terminate.

Example Error

Try running this code to throw up a simple error:

​

SELECT

1/0 as test1

​

SELECT

1/1 as test2

​

You should get the following error:

​

Msg 8134, Level 16, State 1, Line 4

Divide by zero error encountered.

​

Note that the second select will not run!

​

​

Example Error Handling with TRY / CATCH

BEGIN TRY

SELECT

1/0 as test1

END TRY

BEGIN CATCH

SELECT

ERROR_MESSAGE() AS message--you can use a few ERROR functions to pull error information

END CATCH

​

SELECT

1/1 as test2

​

​

Note that the second select will run now!