SQL Try Catch in SQL Server 2005
One of the tsql enhancements in Microsoft SQL Server 2005 is SQL Try Catch improvement.
TSQL Try Catch in SQL Server 2005 enables SQL developers to have more control on error and exception handling in Microsoft SQL Server 2005 and MS SQL Server 2008 databases.
Unfortunately tsql developers can not use Try Catch in SQL Server 2000 databases.
If you are a t-sql developer who also codes in Microsoft VB.NET, Visual C# or other MS Visual languages, the SQL Try Catch syntax in SQL Server will not be difficult to understand for you.
BEGIN TRY
{ t-sql statement block }
END TRY
BEGIN CATCH
[ { t-sql statement block } ]
END CATCH;
As a tsql developer, you place sql codes that you want to handle errors possible in the TRY block.
That means write your t-sql code within BEGIN TRY and END TRY block.
If an error occurs and sql engine raises an error, the sql exception is catched by the CATCH block.
SQL developers can define the sql error exception handling code between the BEGIN CATCH and END CATCH blocks.
Using Try Catch to Retrieve Error Detail
What is very good with BEGIN TRY, END TRY, BEGIN CATCH and END CATCH is that there are ready to use sql recipes that t-sql developers can use to retrieve error information.
Within the BEGIN CATCH and END CATCH block, calling the below system functions sql developers can get detailed information about the sql error.
ERROR_NUMBER() returns the error number of the sql exception.
ERROR_SEVERITY() returns the SQL Server error severity.
ERROR_STATE() will return the error state number.
ERROR_PROCEDURE() can be used to get the SQL Stored Procedure name or the name of the SQL Trigger where the related sql error has occured. This ERROR_PROCEDURE() function is very useful when you try to build generic sql exception handling codes and procedures to identify the source of the error.
ERROR_LINE() will return the line number of the sql code that has raised sql exception within the related sql procedure or sql trigger.
ERROR_MESSAGE() can be used to retrieve the error message text.
Example SQL Try Catch in SQL Server 2005 / SQL Server 2008
One of the most common sql errorsI experience is related with concatenating sql variables.
When one of the concatenated tsql variables is an integer number for example, the conversion failed error message is raised by the SQL Server engine.
The error message I will try to handle is like as shown below:
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value '{0}' to data type int.
The first error that we can try to build an error handler is this conversion errors experienced during string concatenations in sql.
BEGIN TRY
declare @sql nvarchar(max)
declare @sqlvariable int
set @sqlvariable = 100
select @sql = 'The SQL variable value is ' + @sqlvariable + '.'
select @sql
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;
An other very common example can be considered as the divide by zero errors.
The error message is "Divide by zero error encountered."
BEGIN TRY
declare @sqlvariable1 int = 10
declare @sqlvariable2 int = 0
select @sqlvariable1 / @sqlvariable2
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;
Log SQL Server Errors using TSQL Try Catch Block
As a t-sql developer, you may want to log sql errors in your application everytime they occured.
In order to manage this SQL Server error logging, you can use SQL Server Try Catch feature.
First of all build your error log database table.
CREATE TABLE [SQL Error] (
[Error_Number] int,
[Error_Severity] int,
[Error_State] int,
[Error_Procedure] nvarchar(255),
[Error_Line] int,
[Error_Message] nvarchar(max),
[Error_User] nvarchar(100),
[Error_Datetime] datetime
)
Then as a second step, build a generic sql error handling stored procedure to use with sql TRY CATCH method in your sql codes.
CREATE PROCEDURE uspLogSQLError
AS
INSERT INTO [SQL Error] (
[Error_Number],
[Error_Severity],
[Error_State],
[Error_Procedure],
[Error_Line],
[Error_Message],
[Error_User],
[Error_Datetime]
)
SELECT
ERROR_NUMBER(),
ERROR_SEVERITY(),
ERROR_STATE(),
ERROR_PROCEDURE(),
ERROR_LINE(),
ERROR_MESSAGE(),
USER,
GETDATE()
Then alter your SQL stored procedures, sql triggers, and any other t-sql codes as similar to below sample.
CREATE PROCEDURE uspCalculatePercentage (
@dividend int,
@divisor int
)
AS
BEGIN TRY
select (@dividend * 100) / @divisor
END TRY
BEGIN CATCH
EXEC uspLogSQLError;
END CATCH;
-- Let's call Calculate Percentage stored procedure with sample data
EXEC uspCalculatePercentage @dividend = 11, @divisor = 0
Now sql developers can execute sql SELECT statements on [SQL Error] database table in order to see in which sql procedures what kind of sql errors have occured. Also timestamp will guide sql developers when those errors occured.
For me, MS SQL Server 2005 Try Catch feature is a very powerful enhancement when compared with error handling methods in Microsoft SQL Server 2000. T-SQL developers can also use the same sql routines in SQL Server 2008 Try Catch as they just did in Microsoft SQL Server 2005 Try Catch.