Completing the Introduction to Transact-SQL - Error Handling
(Page 2 of 4 )
Error Handling
Let's revisit the RETURN instruction that was presented in the previous article. Simply put, it exits from the procedure or query without relying on conditions. This means that the statements that are next to the RETURN are ignored; the execution ends right as the RETURN instruction is met. Stored procedures can return integers.
When you want to use RETURN within a stored procedure, pay attention, because a NULL value cannot be returned. If you attempt to do this, a warning message as well as a value from the 0 and -14 interval is generated and returned. It is also worth pointing out that with stored procedures, usually a nonzero is akin to a failure while a returned 0 equals success.
Moreover, we have a global variable called @@error that basically always returns the error number for the last T-SQL statement executed. Unless documented otherwise, as I mentioned, a nonzero value means that the execution encountered an error. It is really important to immediately check or backup the value of @@error because it is cleared after each statement execution (to return the latest state).
You see, that's why experienced programmers often say that you need to make compromises and assume that nothing will go wrong. Verifying whether everything is all right after each execution is very boring and monotonous. However, you get my drift.
Moving on, we have the TRY...CATCH construct, which is similar to the exception handling that is possible with other programming languages such as C++, Java, and C#. Thanks to the TRY...CATCH blocks we can implement error handling a bit easier. Things can go very deep (yet be very simple) when you start to nest these blocks. Have no fear!
First of all, we need to understand its methodology. All of the statements that are enclosed within the TRY block are executed. Then the program proceeds to move on if and only if there weren't any errors encountered; then it skips the entire CATCH block. However, if one of the statements caused an error then the control immediately jumps to the beginning of the CATCH block and heads on to execute the statements within.
BEGIN TRY
lots of statements
dozens of statements
END TRY
BEGIN CATCH
catch statements
END CATCH
The above example really points out that by using TRY...CATCH blocks we can write code that is much easier and readable. There are six kinds of "catch functions" which can be used to obtain error information:
ERROR_LINE()
ERROR_MESSAGE()
ERROR_NUMBER()
ERROR_PROCEDURE()
ERROR_SEVERITY()
ERROR_STATE().
The name of these functions are very intuitive and self-explanatory so we won't get into them. Should you call one of the aforementioned functions outside of a CATCH then don't be surprised because they always return NULL.
Warning: Errors that have a lower severity than 10 are considered warnings, and thus aren't considered errors, therefore they do not alter the flow of control. Additionally, neither of the errors that disconnect the server will cause branching to the CATCH block. Processes that are terminated with a KILL command aren't trapped either. Always consider these when implementing error handling.
The last instruction that we'll describe here is the RAISERROR. You as a programmer can opt for either obtaining one of the error messages from the sysmessages table or dynamically creating an error message of your own accompanied by user-specified severity and state information. We usually use these in the case of stored procedures.
RAISERROR ({msg_id | msg_str}{, severity, state})
Notes: msg_id is the user-defined error message number and should be larger than 50,000 if you are creating your own error message. The maximum value is 2^31-1; msg_str stands for the error message per se and its maximum length is 8,000 chars; severity is the user-defined value and it can go from 0 to 18 (any user) and 19 to 25 (sysadmins only); state can be 1-127 and it stands for the invocation state of an error.
Warning: Severity levels 20-25 are considered FATAL. If this occurs then error message gets returned and logged, then the client connection is interrupted.
Next: Playing with Cursors >>
More MS SQL Server Articles
More By Barzan "Tony" Antal
/ 14 
