For SQL Server 2000 and earlier version, the most common way to working error as using @@ERROR function. SQL Server 2005 introduces the construct TRY�CATCH, it improves handling errors. The construct TRY�CATCH is the principal tool for handling error on SQL Server, specifically with exceptions errors. This capability allows you to manage exception errors in similar way as .NET programming languages.
A TRY�CATCH consist in two parts: these are a TRY block followed immediately by a CATCH block. All the TSQL Code must to be on TRY block and the handling error must to be on CATCH block. When in the TRY block does not exist error, the CATCH block is simply skipped. If the TRY block has an error, then the control is transferred to the next CATCH block.
Both, TRY and CATCH block starts with BEGIN TRY and BEGIN CATCH respectively, and ends with END TRY and END CATCH respectively.
You can test the following code in order to see a case without error on TRY block.
-- TSQL WORKING FINE
BEGIN TRY
PRINT 100 / 10
PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'
END TRY
BEGIN CATCH
PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'
END CATCH
The code on TRY block was executed successfully; that�s way the CATCH block was not executed. The result is the following:
10
Working on BLOCK TRY. WITHOUT ERRORS!!!!!!
Now, execute the following code with error on the TRY block:
-- TSQL WITH ERROR - DIVIDE BY ZERO
BEGIN TRY
PRINT 100 / 0
PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'
END TRY
BEGIN CATCH
PRINT 'Working on BLOCK CATCH. WITH ERRORS!!!!!!'
END CATCH
The divide by zero causes an ERROR on the TRY block, it cause that the control was passed immediately to CATCH BLOCK and also cause that the second PRINT on TRY block was not executed. The result is the following.
Working on BLOCK CATCH WITH ERRORS!!!!!!
To obtain information about the error that cause the CATCH block to be executed, SQL Server provides the following system functions:
ERROR_NUMBER() function returns an integer with the number of error
ERROR_SEVERITY() function returns the severity number.
ERROR_STATE() function returns the error state number.
ERROR_LINE() function returns the line number inside the routine that caused the error.
ERROR_MESSAGE() function 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_PROCEDURE() function returns the name of the stored procedure or trigger where the error occurred.
You can execute the following code in order to create a Stored Procedure that encapsulates the system functions mentioned above. Also you can customize the error messages. The following code is customized when the error number 208 (Invalid Object Name) happens, this customized message error will be used later. :
create procedure dbo.sp_err_msg
as
if ERROR_NUMBER() = 208
print 'Oops, the table queried does not exists. '
else
select
ERROR_NUMBER() as ErrorNumber,
ERROR_SEVERITY() as Severity,
ERROR_STATE() as State,
ERROR_LINE() as ErrorLine,
ERROR_MESSAGE() as ErrorMessage,
ERROR_PROCEDURE() as ErrorOnProcedure
GO
You can modify the second code used in this article in order to see how works the error system functions.
BEGIN TRY
PRINT 100 / 0
PRINT 'Working on BLOCK TRY. WITHOUT ERRORS!!!!!!'
END TRY
BEGIN CATCH
exec dbo.sp_err_msg
END CATCH
The divide by zero cause an error on TRY block, then the control was passed to CATCH block and the stored procedure is executed. The result is the following.
Some errors are not handled by construct TRY�CATCH :
- Compilation errors. Ex syntax error.
- Recompilation errors. Ex object name resolution error.
If an compilation or recompilation errors occurs when they are at TRY � CATCH level, those errors are not handled by construct TRY �CATCH. On the other hand, if the error occurs at a lower execution level (ex. Sp_executesql or a stored procedure) the errors are handled by the construct TRY�CATCH.
Run the following code to demonstrate an event that is not handle by TRY�CATCH. The code, execute a SELECT statement to a non existing table, it cause an error:
BEGIN TRY
SELECT * FROM TABLENOTDEFINED
END TRY
BEGIN CATCH
exec dbo.sp_err_msg
END CATCH
Result:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'TABLENOTDEFINED'.
However, if the SELECT statement is executed by a stored procedure the error will occur at a level lower than TRY block. Then, the error will be handled by the construct TRY � CATCH.
create procedure dbo.sp_exec_TABLENOTDEFINED
as
SELECT * FROM TABLENOTDEFINED
GO
BEGIN TRY
exec dbo.sp_exec_TABLENOTDEFINED
END TRY
BEGIN CATCH
exec dbo.sp_err_msg
END CATCH
The table non defined on the Stored procedure cause an error 208, it is handled by TRY ... CATCH, and a customized error message is shown.
Result.
Oops, the table queried does not exists.
Conclusion
TRY..CATCH is the main tool provided by SQL Server to handling errors. To obtain more information about the errors is possible to use a system functions as: ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_LINE(), ERROR_MESSAGE(), ERROR_PROCEDURE().
EmoticonEmoticon