Home > SQL Programming > How to Use Try.. Catch ?

How to Use Try.. Catch ?

August 25, 2010

This post will help you to write Error Handling in T-SQL using Try..Catch block. earlier version we do not have Try..Catch Error handling mechanism where as we normally captured it from the error number function.

Let us see how we are ging to implement Try..Catch in T-SQL. we can use Try..catch in any of the T-SQL block.

Example 1 :
This is simple example of try..Catch block
BEGIN TRY 
SELECT 100/0
END TRY
BEGIN 
CATCH 
SELECT ERROR_NUMBER() AS ErrorNumber,  ERROR_LINE() AS ErrorLine,ERROR_MESSAGE() AS ErrorMessage
END CATCH

This will give you the Error number and the Line Number where the error occurred and the error message. apart from the above function few have few more functions as well . Please find the Details 

  • Error_Number ()   –  Will give you the Error Number
  • Error_Line()  – Will give you the Line number of the Error occurred
  • Error_Message()  – Will give the Exact error message
  • Error_Severity() – Will give the Severity
  • Error_State() –  will give the error state number
  • Error_Procedure –  will give the name of the procedure or trigger where the error occurred.

Now will see bit more detail of Try..Catch Block

Example 2

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 
TABLE [dbo].[product]([product] [int] NOT NULL, 
CONSTRAINT [PK_product] PRIMARY KEY CLUSTERED (
[product]
ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY]

GO
insert into product
select 1

Open new query Window and type the following T-SQL you can see the Primary key violation

BEGIN  TRANSACTION

BEGIN  TRY 
insert into Product
select 1
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
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;
IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO

So we can now implement the Try.Catch to any of your T-SQL.

Advertisements
Categories: SQL Programming
%d bloggers like this: