SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


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;
Code

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;
Code

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;
Code

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
)
Code

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()
Code

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
Code

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.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.