yourfirstserver.com

Try and Catch Fun in Sql Server 2005

Tweet

Introduction

Try and Catch is very popular among the developer community writing code in C#, C++, or other high level languages. The conventional term referring to Try-Catch blocks is Exception Handling. Exception Handling is simply a breach of an application’s predefined assumptions. It enables us to provide a reliable data/process validation mechanism in our applications. SQL Server did not have any close counterpart for it until now. Prior to SQL Server 2005, many of us relied on the variable @@ERROR. If there was any deviant behavior, then @@ERROR would capture a non-zero value to indicate the error code.

Requirement

Please make sure the following are available at hand:

SQL Server 2005 (any version listed here).
AdventureWorks database (can be downloaded from Microsoft).

Keep in mind that AdventureWorks does not come installed by default in the SQL Server Express edition. In short, AdventureWorks is a database for a fictitious company. Sample examples from Microsoft utilize this database as a way to provide proof of concept.

Implementation

Many of us may have seen something like the following as a way to inform errors:

Collapse
Declare @deadline int
set @deadline = 0

SELECT DaysToManufacture / @deadline
from AdventureWorks.Production.Product
WHERE ProductID = 921

if @@ERROR <> 0
begin
print ‘Error occurred’
end

This outputs:

Collapse
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Error Occurred

For the most part, the above works fine, but it’s not as robust as exception handling. It does not give us the flexibility that a try..catch block construct does. Let’s see how this would look in the current world:

Collapse
Declare @deadline int
set @deadline = 0

BEGIN TRY
SELECT DaysToManufacture / @deadline
from AdventureWorks.Production.Product
WHERE ProductID = 921
END TRY
BEGIN CATCH
print ‘Error Occurred’
END CATCH;

This outputs:

Collapse
(0 row(s) affected)
Error Occurred

Does this mean @@ERROR goes away? No, one can still get access to the error value contained in @@ERROR. However, SQL Server 2005 defines several functions whose value can be obtained only within the scope defined within Begin Catch…End Catch. They are ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE(), ERROR_MESSAGE(). BOL or Books Online has a helper procedure namely usp_GetErrorInfo which gets the error related information for us. Later examples from this article will utilize this procedure.

Collapse
CREATE PROCEDURE usp_GetErrorInfo
AS
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GO

Let us modify the first script slightly:

Collapse
Declare @deadline int
set @deadline = 0

BEGIN TRY
SELECT DaysToManufacture / @deadline
from AdventureWorks.Production.Product
WHERE ProductID = 921
END TRY
BEGIN CATCH
EXECUTE usp_GetErrorInfo;
END CATCH;

This outputs:

Let’s modify the second script to check its behavior outside of the Catch block’s scope:

Collapse
Declare @deadline int
set @deadline = 0

SELECT DaysToManufacture / @deadline
from AdventureWorks.Production.Product
WHERE ProductID = 921

SELECT usp_GetErrorInfo;

This outputs:

So far, a variety of ways to handle exceptions has been covered. In SQL Server 2005, it is possible to work with nested Try..Catch blocks. This means that within the scope of a Catch block, one could check whether the logic to cover for predefined cases is breached again. Modifying the earlier query gets to make it look something like:

Collapse
Declare @deadline int
set @deadline = 0

BEGIN TRY
SELECT DaysToManufacture / @deadline
from AdventureWorks.Production.Product
WHERE ProductID = 921
END TRY
BEGIN CATCH
BEGIN TRY
execute usp_GetErrorInfo
select ‘Error occurred at: ‘ + GetDate() – format exception
END TRY
BEGIN CATCH
select ‘Error Occurred’
END CATCH;
END CATCH;

This outputs:

Conclusion

One would wonder why anyone would bother adding the extra bit of syntax. It seems too much structured work and overhead to existing practices. The examples above that used exception handling allowed the execution flow to run smoothly. If you compare the output between @@ERROR and Try..Catch, then it is possible to notice that the SQL Server manager didn’t abruptly go to the tab showing the warning. Instead, it showed the result set that one could easily pick up on the application side. Also, try to see it this way a wise man once said, “Brakes are put in cars so that one could drive freaking fast”. Exception handling in SQL Server 2005 is here to help.