| Introduction | | | | AS |
| Try and Catch is very popular among the developer | | | | SELECT |
| community writing code in C#, C++, or other high | | | | ERROR_NUMBER() AS ErrorNumber, |
| level languages. The conventional term referring to | | | | ERROR_SEVERITY() AS ErrorSeverity, |
| Try-Catch blocks is Exception Handling. Exception | | | | ERROR_STATE() AS ErrorState, |
| Handling is simply a breach of an application's | | | | ERROR_PROCEDURE() AS ErrorProcedure, |
| predefined assumptions. It enables us to provide a | | | | ERROR_LINE() AS ErrorLine, |
| reliable data/process validation mechanism in our | | | | ERROR_MESSAGE() AS ErrorMessage; |
| applications. SQL Server did not have any close | | | | GO |
| counterpart for it until now. Prior to SQL Server | | | | Let us modify the first script slightly: |
| 2005, many of us relied on the variable @@ERROR. | | | | Collapse |
| If there was any deviant behavior, then @@ERROR | | | | Declare @deadline intset @deadline = 0 |
| would capture a non-zero value to indicate the error | | | | BEGIN TRY |
| code. | | | | SELECT DaysToManufacture / @deadlinefrom |
| Requirement | | | | AdventureWorks.Production.Product |
| Please make sure the following are available at hand: | | | | WHERE ProductID = 921 |
| - SQL Server 2005 (any version listed here). | | | | END TRY |
| - AdventureWorks database (can be downloaded | | | | BEGIN CATCH |
| from Microsoft). | | | | EXECUTE usp_GetErrorInfo; |
| Keep in mind that AdventureWorks does not come | | | | END CATCH; |
| installed by default in the SQL Server Express edition. | | | | This outputs: |
| In short, AdventureWorks is a database for a | | | | |
| fictitious company. Sample examples from Microsoft | | | | Let’s modify the second script to check its |
| utilize this database as a way to provide proof of | | | | behavior outside of the Catch block’s scope: |
| concept. | | | | Collapse |
| Implementation | | | | Declare @deadline intset @deadline = 0 |
| Many of us may have seen something like the | | | | SELECT DaysToManufacture / @deadlinefrom |
| following as a way to inform errors: | | | | AdventureWorks.Production.Product |
| Collapse | | | | WHERE ProductID = 921 |
| Declare @deadline intset @deadline = 0 | | | | SELECT usp_GetErrorInfo; |
| SELECT DaysToManufacture / @deadlinefrom | | | | This outputs: |
| AdventureWorks.Production.Product | | | | |
| WHERE ProductID = 921if @@ERROR <> | | | | So far, a variety of ways to handle exceptions has |
| 0beginprint 'Error occurred'end | | | | been covered. In SQL Server 2005, it is possible to |
| This outputs: | | | | work with nested Try..Catch blocks. This means that |
| Collapse | | | | within the scope of a Catch block, one could check |
| Msg 8134, Level 16, State 1, Line 1 | | | | whether the logic to cover for predefined cases is |
| Divide by zero error encountered. | | | | breached again. Modifying the earlier query gets to |
| Error Occurred | | | | make it look something like: |
| For the most part, the above works fine, but it’s | | | | Collapse |
| not as robust as exception handling. It does not give | | | | Declare @deadline intset @deadline = 0 |
| us the flexibility that a try..catch block construct does. | | | | BEGIN TRY |
| Let’s see how this would look in the current | | | | SELECT DaysToManufacture / @deadlinefrom |
| world: | | | | AdventureWorks.Production.Product |
| Collapse | | | | WHERE ProductID = 921 |
| Declare @deadline intset @deadline = 0 | | | | END TRY |
| BEGIN TRY | | | | BEGIN CATCH |
| SELECT DaysToManufacture / @deadlinefrom | | | | BEGIN TRYexecute usp_GetErrorInfoselect 'Error |
| AdventureWorks.Production.Product | | | | occurred at: ' + GetDate() – format exception |
| WHERE ProductID = 921 | | | | END TRY |
| END TRY | | | | BEGIN CATCHselect 'Error Occurred' |
| BEGIN CATCHprint 'Error Occurred' | | | | END CATCH; |
| END CATCH; | | | | END CATCH; |
| This outputs: | | | | This outputs: |
| Collapse | | | | |
| (0 row(s) affected) | | | | Conclusion |
| Error Occurred | | | | One would wonder why anyone would bother adding |
| Does this mean @@ERROR goes away? No, one can | | | | the extra bit of syntax. It seems too much |
| still get access to the error value contained in | | | | structured work and overhead to existing practices. |
| @@ERROR. However, SQL Server 2005 defines | | | | The examples above that used exception handling |
| several functions whose value can be obtained only | | | | allowed the execution flow to run smoothly. If you |
| within the scope defined within Begin Catch...End | | | | compare the output between @@ERROR and |
| Catch. They are ERROR_NUMBER(), | | | | Try..Catch, then it is possible to notice that the SQL |
| ERROR_SEVERITY(), ERROR_STATE(), | | | | Server manager didn’t abruptly go to the tab |
| ERROR_PROCEDURE(), ERROR_LINE(), | | | | showing the warning. Instead, it showed the result |
| ERROR_MESSAGE(). BOL or Books Online has a | | | | set that one could easily pick up on the application |
| helper procedure namely usp_GetErrorInfo which | | | | side. Also, try to see it this way a wise man once |
| gets the error related information for us. Later | | | | said, “Brakes are put in cars so that one could |
| examples from this article will utilize this procedure. | | | | drive freaking fast”. Exception handling in SQL |
| Collapse | | | | Server 2005 is here to help. |
| CREATE PROCEDURE usp_GetErrorInfo | | | | |