Since the advent of SQL Server 7.0, the best way of raising T-SQL related errors back to calling applications has been through the usage of the RAISERROR statement. Figure 1 demonstrates a combination of mandatory parameters (i.e. msg_id/msg_str, severity, state) as well as optional parameters (i.e. WITH option) that ought to be provided for the purposes of successfully using the RAISERROR function.

Figure 1

The content of an exception message raised through the RAISERROR statement can either be hard-coded or retrieved from the sys.messages catalog view based on a corresponding error code number:

1. RAISERROR: Re-throwing Hard-Coded Exceptions

   An example of declaring and raising a user-defined error message within a RAISERROR statement is illustrated in Figure 2. In this example, a SQL Server stored procedure is created under the sample AdventureWorks2012 database that, amongst other things, uses a local variable (@StringVariable) in order to declare and set a custom message to handle an exception of dividing by zero.

   Figure 2
   

   Now, for the purposes of simulating an application calling the stored procedure created in Figure 2, Figure 3 shows a SQL Server 2012 Integration Services (SSIS) Package that uses an SSIS’s Execute SQL Task (EST) – titled RAISERROR Customer Error Messages – to execute a stored procedure that was created in Figure 2.

   Figure 3
   

   The execution of the EST leads to an error and the error message returned to SSIS is shown in Figure 4. It can be noticed that the user-defined custom error message is returned to SSIS as a calling application.

   Figure 4
   

2. RAISERROR: Re-throwing sys.messages-stored Exceptions

   Exceptions stored in the sys.messages can be retrieved by referencing ERROR_MESSAGE, ERROR_SEVERITY and ERROR_STATE system functions. Figure 5 demonstrates a stored procedure that set values of system functions to local variables and RAISERROR then references the locally declared variables.

   Figure 5
   

   Similarly to an example on re-throwing user-defined exceptions, the stored procedure created in Figure 5 is referenced in an SSIS Package (as shown in Figure 6) and the result of SSIS package execution is displayed in Figure 7.

   Figure 6
   

   Figure 7
   

SQL Server 2012 exception handling using THROW statement

The preceding section demonstrated the prevalent ways of handling exceptions in versions of SQL Server prior to SQL Server 2012. This section focuses on the alternative way to exception handling as of SQL Server 2012 by using the THROW statement. Figure 8 shows the syntax that should be followed in order to successfully make use of the THROW statement.

Figure 8

Noticeably in this syntax is the disappearance of mandatory parameters that dictates the usage of RAISERROR. Thus, the THROW statement is simpler and easier to use compared to RAISERROR as you have an option of not parsing any parameters. For instance, Figure 9 shows a stored procedure that, like the example given in the RAISERROR discussion, attempts to divide by zero. However, unlike the numerous local variables that were used in the RAISERROR discussion for catching exceptions, Figure 9 simply shows that it only takes a single line within the CATCH block to handle exceptions when the THROW statement is used.

Figure 9

Furthermore, when the stored procedure created in Figure 9 is used in an SSIS Package (as shown in Figure 10), the exception returned looks similar to the exception that was returned when RAISERROR referenced the sys.messages object (as shown in Figure 11) – except of course that it took less lines of code to achieve this using THROW than it did whilst using RAISERROR.

Figure 10

Figure 11

THROW vs RAISERROR: Different approaches towars SQL Server 2012 exception handling

The preceding section indicated that there is ultimately a simpler and convenient way of re-throwing exceptions when THROW statement is used as compared to RAISERROR. However, the examples demonstrated in preceding sections should not justify an absolute replacement of any T-SQL code that references RAISERROR with a THROW statement. Instead, RAISERROR can still be utilised for some of the requirements that the THROW can’t fulfil.

In this section we will take a closer look at the differences between RAISERROR versus THROW statements. Despite the two statements being used for re-throwing exceptions to calling applications, they tend to behave differently in the way they go about re-throwing exceptions. One significant difference is in the way they handle user exceptions (i.e. exceptions with error codes of 50000 or more) versus system exceptions (i.e. exceptions with error codes less than 50000). Below is a list of points that I have put together to persuade you that in some T-SQL scripts one statement maybe suitable over the other:

1. Accurate Troubleshooting by Error Line Number

   One of the known issues of raising an error using RAISERROR statement is often the incorrect error line number that is returned to the calling application. Noticeably, this incorrect error line number occurs when RAISERROR statement throws a user-defined exception. For instance, refer to Figure 12 whereby after the T-SQL code is executed – the error line number is given to be at 13. However, this is incorrect as the statement that causes a division by zero is actually at line number 2. This is correctly depicted in Figure 13 wherein the THROW statement is used.

   Figure 12
   

   Figure 13
   

2. Accurate Troubleshooting by Error Number

   Another discrepancy that has largely been attributed to the RAISERROR statement is the incorrect error number that is sometimes returned to calling applications. For instance, according to the sys.messages object, the message_id associated with an error incurred by dividing by zero is 8134 (as shown in Figure 14).

   Figure 14
   

   However, the error number returned after the execution of query in Figure 15 is 50000 whereas the query that uses the THROW statement, as shown in Figure 16, is returning the correct error number as 8134.

   Figure 15
   

   Figure 16
   

3. Accurate Termination of T-SQL Statement

   There are sometimes inconsistencies in terms of whether or not T-SQL commands should be executed after RAISERROR statement is executed. Figure 17 shows that despite the exception having being raised for dividing by zero, ‘Do More’ was still printed afterwards. However, having raised an exception as a result of referencing an invalid column, ApexSQL, in Figure 18 – the statement for printing ‘Do More’ was not executed. Fortunately, such inconsistencies are not tolerated in a THROW statement as it can be seen (in both Figures 19 and 20) that commands that appear after the THROW statement are not executed at all.

   Figure 17
   

   Figure 18
   

   Figure 19
   

   Figure 20
   

4. Severity of Exceptions Raised by THROW is Always Set to 16

   SQL Server uses exception severity levels as guide on whether it should return the exception to the client as just a warning or highly critical. Although RAISERROR dynamically returns severity level based on the nature of the exception encountered, the THROW statement invariably returns an exception with a severity level of 16. Figure 21 demonstrates a T-SQL query with conditional logic that throws an exception if the @ColorOfSky variable is not set to Blue. The severity level of this exception is given to be 16. However, the similar T-SQL code in Figures 22 and 23 shows the flexibility of RAISERROR statement in terms of setting and resetting the severity level of an error.

   Figure 21
   

   Figure 22
   

   Figure 23
   

5. Unfortunately, the THROW statement lacks support for the WITH argument. In RAISERROR, there is a choice of three possible values that can be used along the WITH argument. These possible values can be:
   1. LOG
   2. NOWAIT
   3. SETERROR
   
   

   Figure 24 demonstrates a RAISERROR statement that makes use of the WITH LOG argument to write to Windows Application log. Figure 25 displays the application log with the exception that was raised in Figure 24.

   Figure 24
   

   Figure 25
   

6. Practice Statement Termination before learning to THROW

   If you are used to getting away with writing T-SQL code without specifying GO or a semi-colon (;) to indicate the end of a statement – then you will find it difficult to adapt to using the THROW statement. Unless you don’t have any statement preceding the THROW statement, you will not get away with using a THROW statement without specifying T-SQL statement terminators. Furthermore, although RAISERROR permits the use of a RAISERROR statement that is preceded by a statement which does not contain T-SQL statement terminators (as shown in Figure 26), such practice is not tolerated in a THROW statement as shown in Figure 27.

   Figure 26
   

   Figure 27
   

   Figures 28 and 29 demonstrates the acceptable way of using THROW statement with GO and semicolon when it is preceded by other T-SQL statements.

   Figure 28
   

   Figure 29
   

7. Be careful what you RAISERROR for

   All user-defined exceptions that do not exist in the sys.messages catalog view are automatically assigned an error number of 50000. However, should you want to assign an exception to any number greater than 50000, the exception would have to be inserted into the sys.messages catalog view prior to being referenced in your RAISERROR statement. Fortunately, the THROW statement does not require that an error number be registered prior to being cited. For instance, Figure 30 shows that error number (message_id) 56789 does not exist in the sys.messages catalog view. Yet, the THROW statement shown in Figure 31 is able to reference a non-existent error number whereas a similar RAISERROR statement is raising an error as shown in Figure 32.

   Figure 30
   

   Figure 31
   

   Figure 32
   

   Following the successfully registering of the error number (message_id) 56789 in sys.messages catalog view (shown in Figure 33), RAISERROR statement is then able to re-throw an exception with this new error number (as shown in Figure 34).

   Figure 33
   

   Figure 34
   

8. It is Always Best to Know What You Can & Can’t THROW

   Although you can raise both user-defined and system-defined exceptions in a RAISERROR statement, system-defined exceptions can only be raised when the THROW statement is used within a CATCH block. For instance, Figure 35 shows that RAISERROR can directly invoke system-defined error number 40645 whereas the exception returned in Figure 36 indicates that only error numbers within the range of 50000 to 2147483647 can be directly invoked in THROW statement.

   Figure 35
   

   Figure 36
   

   However, error number 8134 is a system exception but notice that (in Figure 37) it can be successfully re-thrown by the THROW statement when the statement is correctly referenced within a CATCH block.

   Figure 37
   

9. Handling Substitution Parameters in the Message Parameter

   The error message retrieved from the sys.messages catalog view can be customised by setting message parameter values. In order to for that to happen, part of the error message should contain the printf function. For instance, let’s add a new message in the sys.message object as shown in Figure 38:

   Figure 38
   

   Now going back to our example of checking for the colour of the sky, Figure 39 demonstrates how RAISERROR statement re-throws a customised message that was added in Figure 38.

   Figure 39
   

   However, substitution parameters in the message parameter are not allowed in a THROW statement. Luckily, adjustments can be made for the purposes of getting the same code to function within a THROW statement. Such adjustments include the usage of the FORMATMESSAGE function. Figure 40 demonstrates how the work around to using substitution parameters can be applied in a THROW statement.

   Figure 40
   

Conclusion

The concept and practice of exception handling in SQL Server is not novel instead statements such as RAISERROR and the TRY/CATCH block has been influential in re-throwing exceptions back to calling applications such as SQL Server Integration Services. The introduction of the THROW statement in SQL Server 2012 has largely made the practice of raising exceptions much more convenient and simple: what used to take a combination of local variables and system functions to successfully re-throw an exception in RAISERROR statement is now easily achievable with a single line when the THROW statement is used. Yet, the THROW statement has various limitations that can only be mitigated by still applying the RAISERROR statement.