Try... Catch in SQL


Try Catch in SQL is the one of the best option to be used with the transaction. We can begin the Transaction immediate after the Begin try and at the end the try we can commit the transaction. It means all the Queries inside the Try executed successfully. And inside the Catch, we can rollback the Transaction since the catch will be executed only if there as there some exception occurs and all or some of the queries inside the try is not executed.

There are few function which will gives the details of the exception occures.


PRINT ERROR_NUMBER() -- Returns the Error Number
PRINT ERROR_MESSAGE() -- Returns the Actual Error Message (In the below case, it will return as Divide by Zero)
PRINT ERROR_LINE() -- Returns the Line numner at which the Exception Occures
PRINT ERROR_PROCEDURE()-- If the try catch inside an Stored Procedure then this function will return the Procedure name

Below is the sample T-SQL with Divide by Zero Exception

 
BEGIN TRY
BEGIN TRAN

DECLARE @dividebyzero INT
SET @dividebyzero = 100
SET @dividebyzero = @dividebyzero/0
COMMIT
END TRY
BEGIN CATCH
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
PRINT ERROR_LINE()
PRINT ERROR_PROCEDURE()
ROLLBack
END CATCH


Related Articles

More articles: SQL try catch SQL exception SQL exception handling

Comments

Author: Venkatesan Prabu .J22 Aug 2010 Member Level: Gold   Points : 0


Very useful article.

Cheers,
Venkatesan Prabu .J

Author: Pugalendiran04 Sep 2010 Member Level: Silver   Points : 0

good



  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name:
    Email: