T-SQL is not really an efficient language for error
handling, and is certainly less robust than error handling in
client-side languages such as C++, Java, and C#. As such, although in
most cases TRY...CATCH blocks work
as expected and catch errors as they should, there are also quite a few
"special cases" that we need to know about, where the behavior is not as
we might expect.
Furthermore, TRY...CATCH
error handling does not really facilitate code reuse. If we want to use
this approach in another stored procedure, we cannot fully reuse our
T-SQL error handling code; we have to cut and paste much of it into that
other stored procedure.
Over the following sections, we'll discuss some of the special cases of which we need to be aware when using TRY...CATCH.
Re-throwing errors
In many cases, we do not wish to handle certain errors in our CATCH
block, and instead want to re-throw them, so that they are handled
elsewhere. In our previous example, where we wished to retry execution
after a deadlock, all other errors were handled by capturing the error
message, using the ERROR_MESSAGE function, and re-throwing the error using RAISERROR. However, the error message on its own is generally insufficient; we should also retrieve the information from the ERROR_LINE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE functions, declare variables to store this information, and then use RAISERROR
to re-throw it. This is very verbose and, as we shall see later, we can
achieve exactly the same outcome in C# by issuing one single command: throw.
However, the real problem with the TRY...CATCH approach is this: RAISERROR cannot preserve ERROR_NUMBER, so when we re-throw an error we often change its error code. For example, consider the ConversionErrorDemo stored procedure in Listing 1. It attempts to cast a string as an integer in the TRY block, and then in the CATCH block invokes two of the seven error handling functions and re-throws the error.
The fact that re-thrown
errors get a different error number means that, when we actually come
to handling conversion errors, both re-thrown and original, we cannot
catch then using the error number alone, as shown in Listing 2.
To catch both the original and re-thrown error, we need to parse the error message, as shown in Listing 3.
Although, this time, we did
catch our re-thrown error, our method is not robust: we can by mistake
catch other errors and handle them as if they were conversion errors, as
shown in Listing 4.
As we have seen, the
inability of T-SQL to re-throw errors may prevent us from robustly
handling re-thrown errors. If we need to re-throw errors, we should do
it on the client.
