Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Exception Handling in SQL Server 2005

By Vasant Raj,

Introduction

The new release of SQL Server 2005 has provided some new and interesting features for developers and administrators. No doubt that more preference is given to the tasks performed by the administrator, but there are various functionality enhancements added for the developers to make their SQL code more powerful and error resistant.

Being a developer, the most useful feature I found was the EXCEPTION handling technique. While programming in .NET or any other language, it is a curse if you are not writing your code in a TRY-CATCH block.

Similarly, when an error is thrown while executing a stored procedure through your application, you only get the information that some xyz error has occurred. This kind of error is OK when your application is in production. But while developing, it is not useful, as you have to find out what the error is, which line or procedure generated the error, etc.

Basic syntax

BEGIN TRY
     --sql_statements
END TRY
BEGIN CATCH
     --sql_statements
END CATCH
[ ; ]

Important Points

Here are some important things to know about TRY-CATCH

  1. To catch all the errors which are of severity greater than 10 and which do not close the database connection.
  2. Each TRY block is associated with only one CATCH block and must be followed immediately by an associated CATCH block.
  3. The CATCH block is executed only when any error occurs. If no errors, then the next statement after the END CATCH is executed.
  4. The TRY-CATCH block should be in the same batch i.e., A single TRY-CATCH block cannot span multiple batches. Similar for stored procedures or triggers.
  5. The TRY-CATCH construct cannot be used within a user-defined function.
  6. TRY-CATCH constructs can be nested.
  7. On ERROR, the control passes to the first statement in the associated CATCH block.
  8. Errors trapped by a CATCH block are not returned to the calling application. If any of the error information must be returned to the application, the code in the CATCH block must do so using mechanisms, such as SELECT result sets or the RAISERROR and PRINT statements.
  9. If an ERROR occurs in the CATCH block and if the CATCH block contains a nested TRY-CATCH, it will be passed to the nested CATCH block. If there is no nested TRY-CATCH construct, the error is passed back to the caller.
  10. The CATCH block does not handle compile errors, such as syntax errors, that prevent a batch from executing. Also, object name resolution errors are not handled by the CATCH block.
The following functions are used to get the ERROR details:
  • ERROR_NUMBER()
  • ERROR_SEVERITY()
  • ERROR_STATE()
  • ERROR_PROCEDURE()
  • ERROR_LINE()
  • ERROR_MESSAGE()
All the above functions can be used in CATCH block, and returns specific values related to the ERROR generated. If they are used outside CATCH block, then all of them will return NULL.

How to USE it?

STEP I: You can create a common procedure that gets you the details of the generated ERROR.
CREATE PROCEDURE ErrorDetails
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
This procedure can be executed in your CATCH block.

STEP II: Your actual code where all the processing is done.

BEGIN TRY
	SELECT GETDATE()
      	SELECT 1/0
END TRY
BEGIN CATCH
       EXEC ErrorDetails
END CATCH;
On execution of the code in STEP II, the result obtained is:

In SQL Server 2000, the techniques previously used to perform error checking was through @@ERROR and performing GOTO or RETURN. Using TRY-CATCH in your SQL code will help you to monitor your code and handle errors easily.

Total article views: 22984 | Views in the last 30 days: 23
 
Related Articles
FORUM

try catch blocks

try catch blocks

FORUM

Working with CATCH Block continuity

Working with CATCH Block continuity

FORUM

Nested Try...Catch

Error always thrown to second Catch block

ARTICLE

Display All Errors Fired in a Restore Procedure

Display all errors thrown within a catch block by a restore statement in a stored procedure using ex...

FORUM

Try Catch alters behaviour of existing procedures

try catch alters behaviour of existing procedures

Tags
news    
sql server 2005    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones