Good error handling can be critical to
writing robust code and ensuring predictable behavior should things
go wrong. We always want to be certain that our database will be left in
a clean and desirable state, which can be challenging if, say, a stored
procedure errors part way through a series of inter-dependent steps.
As a basic starting point for basic handling of problem scenarios it's good
practice to contain code within transactions:
BEGIN
TRAN
--DO
SOMETIHNG
COMMIT
If multiple steps are contained within
the transaction, and any one should fail, then all the steps will be
rolled back, leaving the database in the same state it was before the transaction began.
With SQL 2005, TRY..CATCH
blocks were introduced. Using this feature we can greatly enhance
the error handling. We can now “try” to do our data manipulation
and should any step fail, we can “catch” the error and perform further "clean up" steps as a result.
A simple example might be to open a
transaction in the TRY
block, roll it back in the CATCH,
else commit it:
BEGIN
TRY
BEGIN
TRAN
--DO
SOMETHING
END
TRY
BEGIN
CATCH
IF
@@TRANCOUNT
>
0
ROLLBACK
END
CATCH
IF
@@TRANCOUNT
>
0
COMMIT
For more details on useful global variables such as @@TRANCOUNT
see my post on SQL Server Global Variables.
The above code allows us to cleanly
handle error scenarios and greatly improve the robustness of our code
and therefore our database. However, the above example does not
actually let us know any details about the error that has occurred.
To do that, we need to capture the error information:
BEGIN
TRY
SELECT
10/0
END
TRY
BEGIN
CATCH
SELECT
ERROR_MESSAGE()
AS
ErrorMessage,
ERROR_NUMBER()
AS
ErrorNumber,
ERROR_SEVERITY()
AS
ErrorSeverity,
ERROR_STATE()
AS
ErrorState,
ERROR_LINE()
AS
ErrorLine
END
CATCH
In the above script I have forced a
“divide by zero” error in the TRY
block. When this error is thrown, the code will jump into the CATCH
block and execute the select statement:
ErrorMessage
ErrorNumber ErrorSeverity ErrorState
ErrorLine
-------------------------------------
----------- ------------- ----------- -----------
Divide
by zero error encountered. 8134 16 1
3
Note that the actual error has not been
thrown, and the code has completed successfully without any
problems. We are simply displaying the results of the various error
functions.
We can make this even more powerful if
we use the RAISERROR
function. This will allow us to actually throw an error message back
to the calling application. The advantage this gives us is that we
can capture the error from the TRY,
perform remedial action in the CATCH,
and then let the calling application know that there was an error we
had to clean up:
DECLARE
@ErrorMsg VARCHAR(500)
BEGIN
TRY
BEGIN
TRAN
SELECT
10/0
END
TRY
BEGIN
CATCH
SELECT
@ErrorMsg =
'Error:
' +
ERROR_MESSAGE()
+CHAR(13)
+'Severity:
' +
CAST(ERROR_SEVERITY()
AS
VARCHAR(100))
+CHAR(13)
+'State:
' +
CAST(ERROR_STATE()
AS
VARCHAR(100))
+CHAR(13)
+'Number:
' +
CAST(ERROR_NUMBER()
AS
VARCHAR(100))
+CHAR(13)
+'Line:
' +
CAST(ERROR_LINE()
AS
VARCHAR(100))
IF
@@TRANCOUNT
>
0
ROLLBACK
RAISERROR(@ErrorMsg,
16,10)
END
CATCH
IF
@@TRANCOUNT
>
0
COMMIT
-----------
(0
row(s) affected)
Msg
50000, Level 16, State 10, Line 21
Error:
Divide by zero error encountered.
Severity:
16
State:
1
Number:
8134
Line:
5
The result is that the error is
captured and handled - the transaction is rolled back. Using the
RAISERROR function,
however, we have build our own custom error message to return to the
calling application. In the example above, this message lets us know
even the exact line that caused the error. This message can be
enriched to include as much information as you wish, which can
greatly ease debugging of the problem.
(It's worth noting that the severity of
the error I have set to 16. Different severity numbers can have
different effects in different places, and typically 11-18 are the
range for user defined errors. See the MSDN link at the end of this
article for more details.)
Finally, RAISERROR
can be used to throw msg_id's, and this is the more standard SQL
behaviour. The list of standard error messages that can be thrown
are contained in the sys.messages
table. You can insert your own (with an
id >50000) and then use RAISERROR
to throw these when required.
Error handling can be used to powerful
effect using the techniques described. If you have any techniques
you use or ideas on how to improve on the methods described here,
please feel free to share them.
MSDN - RAISERROR:
