Using the New THROW Keyword in SQL Server.

Introduction :
When SQL Server 2005 introduced the TRY...CATCH blocks, SQL Server developers were finally able to use structured exception handling to their code. However, passing error information back to the application from the CATCH block using a RAISERROR statement is not as simple as it seems. To ensure that the correct information is passed back to the caller, you have to add your error information to the sys.messages table. That's easy enough to do, but if your application moves onto another server, there's a risk that you'll forget to add the error to sys.messages on the new server and your application may not function correctly.



SQL Server "Denali" introduces the THROW keyword, which extends the exception handling functionality of SQL Server 2008. It eliminates the issue of having to define errors in sys.messages and also enables you to handle an error and then rethrow that error back to the calling code.



So let's look at some examples of how you can use THROW in your Transact-SQL code. If you are implementing search functionality, you might want to customize the information returned when a SELECT statement finds no records. In this situation, you can simply use the THROW statement to return your own error message to the calling application.



SELECT * FROM Person.Person

WHERE LastName = 'Smyth'



IF @@ROWCOUNT = 0

THROW 50001, 'No results found.', 1



You can see in this example that I've used an error number greater than 50000. This is a requirement of THROW to avoid replicating any of the system error numbers. I've specified a meaningful message to be returned to the calling application that can be used directly in the calling code and the state parameter, which you can use to locate where the error originated.



The output when you run this code will be:



(0 row(s) affected)

Msg 50001, Level 16, State 1, Line 7

No results found.



You can also use THROW in a TRY ... CATCH block. This enables you to execute Transact-SQL code such as rolling back a transaction or logging information before passing the error back to the calling application.



BEGIN TRY

DECLARE @BusEntityID int;

SET @BusEntityID = (SELECT MAX(BusinessEntityID) FROM Person.BusinessEntity)

INSERT Person.Person(BusinessEntityID, PersonType, NameStyle, FirstName, LastName)

VALUES(@BusEntityID, 'EM', 0, 'Lin', 'Joyner')



END TRY



BEGIN CATCH

-- Handle the error.

-- Log the error in the SQL Server application log.

THROW

END CATCH



By using the THROW statement on its own, you will simply rethrow the existing error that has occurred. This is useful when the SQL Server error message is meaningful to the user or application. If you run this code with a non-unique value for the BusinessEntityID field, you will see the following error:



(0 row(s) affected)

Msg 2627, Level 14, State 1, Line 5

Violation of PRIMARY KEY constraint 'PK_Person_BusinessEntityID'. Cannot insert duplicate key in object 'Person.Person'. The duplicate key value is (20780).



You can use the parameters of THROW to make the error information more meaningful to the calling application or user as shown in the following example.



BEGIN TRY

DECLARE @BusEntityID int;

SET @BusEntityID = (SELECT MAX(BusinessEntityID) FROM Person.BusinessEntity)

INSERT Person.Person(BusinessEntityID, PersonType, NameStyle, FirstName, LastName)

VALUES(@BusEntityID, 'EM', 0, 'Lin', 'Joyner')



END TRY



BEGIN CATCH

-- Handle the error.

-- Log the error in the SQL Server application log.

THROW 51111, 'Invalid business entity id.', 2

END CATCH



In this case, the output when you violate the primary key will be as follows:



(0 row(s) affected)

Msg 51111, Level 16, State 2, Line 9

Invalid business entity id.



You'll notice that the severity level when you throw your own error is defaulting to 16. You cannot define a severity level for custom errors, however when you rethrow a SQL Server error, the original severity level is retained. You can only rethrow errors from inside a CATCH block, if you are outside of the CATCH block, you must specify the parameters.



So, as you can see, the new THROW statement in SQL Server "Denali" extends the structured exception handling capabilities of earlier versions of SQL Server.