 |
||||||||||
| ||||||||||
| ||||||||||
Database »
Database »
General
Intermediate
Using TRY...CATCH in Transact-SQLBy Himanshu Kumar SinhaI will discuss the Try & catch errorhandling feature of the SQL SERVER 2005 (Yukon) |
C++, SQL, Windows, Visual Studio, SQL 2005, SQL Server, DBA, Dev Posted: 25 Oct 2005 Updated: 25 Oct 2005 Views: 36,320 Bookmarked: 8 times |
||
|
|
|||
|
Advanced Search Sitemap |
|
|
|
||||||||||||||||
Introduction
Using TRY...CATCH in Transact-SQL
Purpose of this Article
I will discuss the new feature ( TRY ........CATCH) for errorhandling in SQL SERVER 2005
Microsoft have introduced the TRY & CATCH syntax (TAC) for error handling is T-SQL , in SQL server 2005 . The .Net must have used this syntax before also , as this TAC was initially introduced and now they are introducing this in SQL also .
How is works ?
As i have mentioned this TAC block is very similar to what be use in other .net langauges . The idea is faily simple
When an error condition is detected in a Transact-SQL statement contained in a TRY block, control is passed to a CATCH block where it can be processed.
Begin Try
The_Query_for_which_we_need_to_do_the_ Error_Handling
End Try
Begin Catch
If there is some error in the query within the Try block , this flow
will be passed to this Catch block .
Do for all the error , the TAC blocks works ?. Now we will talk on that
below
End catch
If there are no errors inside the TRY block, control passes to the statement
immediately after the associated END CATCH statement. If the END CATCH
statement is the last statement in a stored procedure or trigger, control is
passed to the statement that invoked the stored procedure or trigger.
Do for all the error , the TAC blocks works ?
No , please do take care of the following points
- A TRY block must be followed immediately by a CATCH block.
TRY…CATCH constructs can be nested, which means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block associated with the nested TRY block.
BEGIN TRY
Print ' I am level 1 '
BEGIN TRY
Print ' I am level 2 '
SELECT 1/0;
END TRY
BEGIN CATCH
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;
Print ' I am going out level 2 '
END CATCH
Print ' I am level 1 again '
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
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;
END CATCH
Output
I am level 1
I am level 2
-----------
(0 row(s) affected)
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
8134 16 1 NULL 5 Divide by zero
error encountered.
(1 row(s) affected)
I am going out level 2
I am level 1 again
In the code above the error is thrown from the Level 2 TAC block
- To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.
- Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated.
- Errors with a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
From the above two statement it is clear that
Try & Catch block will work for
Errors with a severity of 10 < TRy & Block <= Errors with a severity of 20
Error Functions
TRY…CATCH uses error functions to capture error information.
ERROR_NUMBER() returns the error number.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.
ERROR_SEVERITY() returns the error severity.
ERROR_STATE() returns the error state number.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
Error information is retrieved using these functions from anywhere within the scope of the CATCH block of a TRY…CATCH construct. The error functions will return NULL if called outside the scope of a CATCH block. Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed within the CATCH block. By doing this, it will not be necessary to type error handling code in every CATCH block.
Will the TRY AND Catch block will catch Compile errors, such as syntax errors that prevent a batch from executing. ?
No , the TAC block will not catch the compile errors , if it is not called in the from of dynamic query or in some SP
In the below code the table does not exist , so we will get the error while we want to complie the code .The TAC block does not play any role here
BEGIN TRY
-- This PRINT statement will run since the error
-- occurs at the SELECT statement.
PRINT N'Starting execution'
SELECT * FROM NonExistentTable
-- This SELECT statement will generate an object name
-- resolution error since the table does not exist.
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
OutPut :
Starting execution
Msg 208, Level 16, State 1, Line 6
Invalid object name 'NonExistentTable'.
I will try to put the above logic using a dynamic sql . On doing so the code will compile , but will through a error , which will be catched by the TAC block
-- Dynamic query
BEGIN TRY
-- This PRINT statement will run since the error
-- occurs at the SELECT statement.
PRINT N'Starting execution';
DECLARE @SQL NVARCHAR(2000)
SET @SQL = 'SELECT * FROM NonExistentTable;'
-- This SELECT statement will generate an object name
-- resolution error since the table does not exist.
EXEC sp_executesql @SQL
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
Output :
Starting execution
ErrorNumber ErrorMessage
----------- ------
208 Invalid object name 'NonExistentTable'.
(1 row(s) affected)
Dealing with Transaction
In the TRY statment it may happen that we open a Transaction , but for some reason , the statement went to the respective catch block , then how is the open Transaction is going to behave .
In this state, however, the locks acquired by the transaction are maintained, and the connection is also kept open. The transaction's work is not reversed until a ROLLBACK statement is issued.
The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. The following code example generates an error from a DDL statement and uses XACT_STATE to test the state of a transaction in order to take the most appropriate action.
CREATE TABLE TEMP ( A INT )
BEGIN TRY
-- BEGIN Tran
ALTER TABLE TEMP
DROP COLUMN author
COMMIT TRAN
END TRY
BEGIN CATCH
-- Execute the error retrieval routine.
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;
PRINT ' *****Value of XACT_STATE ****'+CONVERT(VARCHAR,XACT_STATE())
END CATCH
GO
Output
ErrorNumber ErrorSeverity ErrorState ErrorProcedure ErrorLine ErrorMessage
----------- ------------- ----------- --------------------------------------
3930 16 1 NULL 3 The current transaction
cannot be committed and
cannot support
operations that write
to the log file. Roll
back the transaction.
(1 row(s) affected)
*****Value of XACT_STATE ****-1
| You must Sign In to use this message board. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 25 Oct 2005 Editor: |
Copyright 2005 by Himanshu Kumar Sinha Everything else Copyright © CodeProject, 1999-2008 Web15 | Advertise on the Code Project |