Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Exception Handling by TRY…CATCH

  Author : Shailendra Chauhan
Posted On : 17 Apr 2011
Total Views : 159,254   
Updated On : 24 Sep 2016
 

Like C#, SQL Server also has an exception model to handle exceptions and errors that occurs in T-SQL statements. To handle exception in Sql Server we have TRY..CATCH blocks. We put T-SQL statements in TRY block and to handle exception we write code in CATCH block. If there is an error in code within TRY block then the control will automatically jump to the corresponding CATCH blocks. In Sql Server, against a Try block we can have only one CATCH block.

TRY..CATCH Syntax

 BEGIN TRY
--T-SQL statements
--or T-SQL statement blocks
END TRY
BEGIN CATCH
--T-SQL statements
--or T-SQL statement blocks
END CATCH 

Error Functions used within CATCH block

  1. ERROR_NUMBER()

    This returns the error number and its value is same as for @@ERROR function.

  2. ERROR_LINE()

    This returns the line number of T-SQL statement that caused error.

  3. ERROR_SEVERITY()

    This returns the severity level of the error.

  4. ERROR_STATE()

    This returns the state number of the error.

  5. ERROR_PROCEDURE()

    This returns the name of the stored procedure or trigger where the error occurred.

  6. ERROR_MESSAGE()

    This returns the full text of error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Exception handling example

 BEGIN TRY
DECLARE @num INT, @msg varchar(200)
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
PRINT 'Error occured that is'
set @msg=(SELECT ERROR_MESSAGE())
print @msg;
END CATCH
GO 
 BEGIN TRY
DECLARE @num INT
---- Divide by zero to generate Error
SET @num = 5/0
PRINT 'This will not execute'
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 

Note

  1. A TRY..CATCH block combination catches all the errors that have a severity between 11 and 19.

  2. The CATCH block is executed only if there is an error occurs in T-SQL statements within TRY block otherwise the CATCH block is ignored.

  3. Each TRY block is associated with only one CATCH block and vice versa

  4. TRY and CATCH blocks can’t be separated with the GO statement. We need to put both TRY and CATCH blocks within the same batch.

  5. TRY..CATCH blocks can be used with transactions. We check the number of open transactions by using @@TRANCOUNT function in Sql Server.

  6. XACT_STATE function within the TRY..CATCH block can be used to check whether a open transaction is committed or not. It will return -1 if transaction is not committed else returns 1.

Summary

In this article I try to explain Exception handling in Sql Server with example. I hope after reading this article you will be know how to handle exception in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.



Free Interview Books
 
COMMENTS (0)
6 MAR
Master Class Fast Track MVC 5 with Angular2 Development (Online)

10:30 AM -03:30 PM IST (Fast Track)

Know More
6 MAR
Angular 2 Master Class Fast Track Online Development Training (Online)

10:30 AM- 01:30 PM IST

Know More
27 FEB
ASP.NET MVC with Angular2 Development (Online)

07:00 AM - 09:00 AM IST( MON, WED, FRI)

Know More
19 FEB
Xamarin Forms : Build Cross-platform Apps (Classroom)

3:00 PM-4:30 PM IST

18 FEB
ASP.NET MVC with Angular2 Development (Classroom)

05:00 PM-06:30 PM

14 FEB
.NET Development (Classroom)

11:00 AM-12:00 PM

31 JAN
ASP.NET MVC with Angular2 Development (Online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

30 JAN
NODEJS & MEAN Stack 2.x Development (Online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (Classroom)

04:00 PM-05:30 PM

10 JAN
ASP.NET MVC with AngularJS Development (Online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

31 DEC
ASP.NET MVC with AngularJS Development (Classroom)

09:30 AM-11:00 AM

5 NOV
ASP.NET MVC with AngularJS Development (Classroom)

08:00 AM-09:30 AM

BROWSE BY CATEGORY
 
 
LIKE US ON FACEBOOK
 

Professional Speaks

+