Implement error handling with T-SQL

On 11 Apr, 2015

T-SQL

By : Charith Silva

No Comments

Error handling in T-SQL can be complex.
TRY…CATCH certainly is the best way to handle errors in T-SQL.

BEGIN TRY
   BEGIN TRANSACTION
   ------
   ------
   ------
   COMMIT TRANSACTION;
 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;

   ROLLBACK TRANSACTION;
 END CATCH

BEGIN TRY

BEGIN TRANSACTION

------

COMMIT TRANSACTION;

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;

ROLLBACK TRANSACTION;

END CATCH

About The Author

Charith Silva

Charith Silva is a Microsoft certified SQL Server developer and database administrator who was graduated at Buckinghamshire New university in the UK. His career was started in 1998, primarily into Web application development, and later diversified into database development. He has got a vast experience in SQL Server database development, Database administration and Business Intelligence development. He believes that sharing the knowledge is key to the success.

Number of Posts : 87

All Posts by : Charith Silva