SQL Server 2005 offers a more robust set of tools for handling errors than in previous versions of SQL Server. Deadlocks, which are virtually impossible to handle at the database level in SQL Server 2000, can now be handled with ease. By taking advantage of these new features, you can focus more on IT business strategy development and less on what needs to happen when errors occur. In SQL Server 2005, @@ERROR variable is no longer needed after every statement executed, as was the case in SQL Server 2000. SQL Server 2005 provides the TRY…CATCH construct, which is already present in many modern programming languages. TRY/CATCH helps to write logic separate the action and error handling code. The code meant for the action is enclosed in the TRY block and the code for error handling is enclosed in the CATCH block. In case the code within the TRY block fails, the control automatically jumps to the CATCH block, letting the transaction roll back and resume execution. In addition to this, the CATCH block captures and provides error information that shows you the ID, message text, state, severity and transaction state of an error.
Functions to be used in CATCH block are :
- ERROR_NUMBER: returns the error number, and is the same value of @@ERROR.
- ERROR_SEVERITY: returns the severity level of the error that invoked the CATCH block.
- ERROR_STATE: returns the state number of the error.
- ERROR_LINE: returns the line number where the error occurred.
- ERROR_PROCEDURE: returns the name of the stored procedure or trigger for which the error occurred.
- ERROR_MESSAGE: returns the full message text of the error. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
You can use these functions anywhere inside a CATCH block, and they will return information regarding the error that has occurred. These functions will return the value null outside of the CATCH block.
Syntax:
BEGIN TRY
{ sql_statement |
statement_block }
END TRY
BEGIN CATCH
{ sql_statement |
statement_block }
END CATCH
The TRY or CATCH block can contain a single T-SQL statement or a series of statements. The CATCH block must follow immediately after the TRY block. The TRY/CATCH block cannot span more than a single batch. In addition, TRY/CATCH block cannot span an IF/ELSE statement.
Example of TRY…CATCH:
BEGIN TRY DECLARE @X INT ---- Divide by zero to generate Error SET @X = 1/0 PRINT 'Command after error in TRY block' END TRY BEGIN CATCH PRINT 'Error Detected' END CATCH PRINT 'Command after TRY/CATCH blocks'
Above code will return following result:
Error Detected
Command after TRY/CATCH blocks
If all the statements within the TRY block are executed successfully, then processing does not enter the CATCH block, but instead skips over the CATCH block and executes the first statement following the END CATCH statement. Removing SET statement in above code PRINT ‘Error Detected’ statement is not executed, but the PRINT statement within the TRY block is executed, as well as the PRINT statement after the TRY/CATCH block. TRY/CATCH blocks can be nested.
Limitation of TRY…CATCH:
- Compiled errors are not caught.
- Deferred name resolution errors created by statement level recompilations. (If process is terminated by Kill commands or broken client connections TRY…CATCH will be not effective)
- Errors with a severity greater than 10 that do not terminate their database connection are caught in the TRY/CATCH block.
For errors that are not trapped, SQL Server 2005 passes control back to the application immediately, without executing any CATCH block code.
Similar example of TRY…CATCH which includes all the ERROR functions:
USE AdventureWorks; GO BEGIN TRY -- Generate a divide-by-zero error. 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; END CATCH; GO
Reference : Pinal Dave (https://blog.sqlauthority.com) , BOL
47 Comments. Leave new
CAN WE USE TRY CATCH IN FUNCTIONS?
Hi,
I tried to insert data into a table which doesn’t exists from a SP. In this case, I am not able to trap the error in CATCH block and it breaks while running in between.
BEGIN TRY
–Insert statement
END TRY
BEGIN CATCH
PRINT ‘Error Detected’
END CATCH
Output:
Msg 208, Level 16, State 1, Procedure usp_Calculation, Line 805
Invalid object name ‘tbl_Allocation_Debug’.
Check upfront if object exists or not.
If exists(
Select * from information_schema.tables where table_name=
) … then go ahead inserting data.
Hi ,
kindly let me know how to capture the exception raised by the below sql and i want to use insert Try and Catch in the sql below.
DECLARE @triprequest varchar (50)
DECLARE @xml_str varchar(4000)
DECLARE @hdoc int
declare @temp_table_travel_Req_xml_det table ([triprequest] varchar (50),[name] varchar (50), [created_by] varchar (50), [action] varchar (16), [created_on] datetime)
DECLARE xml_cur cursor for
SELECT CMTRAVELCOMMENTS.tripRequest triprequest, cast(cast(CMTRAVELCOMMENTS.config as
varbinary(8000)) as VARCHAR(8000)) xml_str
FROM CMTRAVELCOMMENTS, PCMTRAVELREQUEST
WHERE
PCMTRAVELREQUEST.NODESTATE = ‘Approved request’
and CMTRAVELCOMMENTS.tripRequest=PCMTRAVELREQUEST.tripRequest
OPEN xml_cur
FETCH xml_cur into @triprequest,@xml_str
WHILE(@@fetch_status=0)
BEGIN
EXEC sp_xml_preparedocument @hdoc OUTPUT, @xml_str
INSERT into temp_table_travel_Req_xml_det
SELECT @triprequest,*
FROM OPENXML (@hdoc,’/cm-comments/comments’, 3)
WITH ([name] varchar (50), [created_by] varchar (50), [action] varchar (16), [created_on] datetime)
EXEC sp_xml_removedocument @hdoc
print @triprequest
FETCH xml_cur into @triprequest,@xml_str
END
CLOSE xml_cur
DEALLOCATE xml_cur
Hello Sir,
Is there any way to catch error with severity less than 10.
I have a Stored Proc wherein dynamic sql query is generated. If the query is wrong, How can i catch the exception?
If the query generated can be wrong, than the user input is wrong and hence i need to update another table.
Can u pls help ..
Thanks
Geervani
Is it possible for try..catch to be global for some reason? We are seeing stored procedures calling an error statement even though there is no error checking (try catch or otherwise) on that particular statement. For example, in the sp we might do inserts and on those we check @@error and we always do an insert into table called errors if we see a failure, but we have some update statements that we expect to not find anything to update in certain conditions so we do not check for any errors yet when they do not work we are getting an error on not being able to insert into the table errors and the sp bombs. Is it possible for there to be a global try catch that gets called somehow?
I have a stored procedure that updates a linked server. The linked server is returning an error (because the update would violate a business rule).
ERROR_MESSAGE() has ‘Cannot fetch a row from OLE DB provider “” for linked server “”.’
This is OK as far as it goes. But when I debug this in VS 2008, the Debug Window displays this much more detailed info:
OLE DB provider “” for linked server “” returned message “Cursor fetch row failed.
exception 98
VER2021-Service Records can not overlap for DSP.”.
How can I access that more detailed message within my stored procedure’s CATCH block?
i want to know in sql script what type of
exception are generate like FOREGIN KEY,PRIMARY KEY, UNIQUE KEY VIOLATION
so that i take a step to generate a appropriate message to
user that it understand what is error.
Pinal,
Can you please provide some more info on NESTED TRY CATCH blocks and how the control passes from one to another during calling one stored procedure from another.
I have the following situation
CREATE PROCEDURE sp_a(
….)
AS
BEGIN…
..
BEGIN TRY — Outer Try block
UPDATE TABLE a…..WHERE …
EXEC sp_b
UPDATE TABLE….
SET a…..
WHERE….
END TRY
BEGIN CATCH
SET @ErrorMsg = ‘ErrNo: ‘ + ERROR_NUMBER() + ‘ Msg: ‘ +
ERROR_MESSAGE()
END CATCH
END
—- End of Stored Proc – sp_a
CREATE PROCEDURE sp_b(..
…)
…
BEGIN TRY — Nested Try Block – Inner block
INSERT INTO TABLE…..
VALUES(…)
END TRY
BEGIN CATCH
SET @ErrorMsg = ‘ErrNo: ‘ + ERROR_NUMBER() + ‘ Msg: ‘ +
ERROR_MESSAGE()
END CATCH
END —
—- End of Stored Proc sp_b
So in this case if the sproc sp_b returns error, does the stored procedure capture that error and goes to the Outer CATCH block or do I have log the Inner CATCH error separately into a table.
Any feedback would be much appreciated
I understand the Try Catch, but how can I catch that the link server is down, and test it without taking down the link server?
BEGIN TRY
–SELECT 1/0
SELECT state_desc FROM LinkedServerName.master.sys.databases WHERE [Name] = ‘DBaseName’
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;
END CATCH
Hi,
I am using sqlserver 2005.if i execute following lines of code in sqlserver catch bolck not executing.Since i dont have Permanenttable and Temporarytable in my db.
BEGIN TRY
insert Permanenttable
select * from Temporarytable
END TRY
BEGIN CATCH
print ‘catch fired’
END CATCH
The invalid object name error cannot be jhandled by CATCH block
I understand try catch blocks having some limitation.
But how can i handle this type of exception?
I am having scenario like following
Create procedure sp1
as
Begin
Begin Try
Begin Tran
Declare cursor1 cursor for …………
………………
While @@Fetch_status=0
Begin
Declare cursor2 cursor for …………
………………
While @@Fetch_status=0
Begin
Declare cursor3 cursor for …………
………………
While @@Fetch_status=0
Begin
insert Permanenttable
select * from Temporarytable
End
End
End
IF @@TRANCOUNT > 0
COMMIT TRAN
End Try
Begin catch
Rollback tran
IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor1’) >= 0
BEGIN
CLOSE Cursor1
DEALLOCATE Cursor1
END
IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor2’) >= 0
BEGIN
CLOSE Cursor2
DEALLOCATE Cursor2
END
IF CURSOR_STATUS(‘GLOBAL’, ‘Cursor3’) >= 0
BEGIN
CLOSE Cursor3
DEALLOCATE Cursor3
END
End catch
END
If exception raised at the time of inserting then it will terminate.But my all cursors will be open state only.
If next time i execute this SP it will give Cursor1 already exist and transaction opened error.
So can any one tell me how can i acheive this type of scenario.
ALTER PROC SAMPLE1
AS
BEGIN
BEGIN TRY
PRINT ‘1’
DECLARE @SQL VARCHAR(MAX)
SET @SQL = ‘insert Permanenttable select * from Temporarytable’
EXEC(@SQL)
— select 1/0
PRINT ‘2’
END TRY
BEGIN CATCH
print ‘catch fired’
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
END
Yes. But it is not possible without dynamic sql
which one we should use transaction/@@error or try/catch.
The best way to test for a linked server is to use SQL’s built in system procedure:
Ex: EXEC sys.sp_testlinkedserver ‘yourlinkedserver’
can we write insert statement in a trigger catch block in sql server 2005
Hi….. Pinal
i m new to sql server, so i wanted to know that how can i return the error code using error_number() and error message using error_message() with variable to the calling…………. like we can return in oracle using sqlcode, sqlerrm
Hi… Pinal
I have been following your blog and failed to understand why the stored procedure gets printed “NULL” when it goes to catch. It is returning the stored procedure name only when there is foreign key relationship violation. or else every time it is “NULL” Could you please help me .
Thanks… was simple in understanding
can we use finally block with try catch in sql server any version.if we not use so why we not use.