Monday, November 2, 2015

TRY...CATCH usage in (T-SQL)

Try catch got same functionality as other programming languages like c++,c#,c..etc. Microsoft introduced try catch mechanism from SQL Server 2005. This functionality used for error handling in any programming language and same concept used in T-SQL scripts.Work flow of try catch can be explained through syntax

Syntax:- 

BEGIN TRY
T-SQL Statements
(or)
Block of  T-SQL Statements
END TRY
BEGIN CATCH
T-SQL Statements
(or)
Block of  T-SQL Statements
END CATCH

The above syntax shows that at  BEGIN TRY part will start execution of T-SQL statements and if there are no exceptions found in TRY block control directly passed to the END CATCH which is the end part of stored procedure or trigger. Check the below stored procedure for try catch example.

CREATE PROCEDURE uspTryCatchTest
AS
BEGIN TRY
    SELECT 1/0
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

In above stored procedure begin catch statement block contain error functions they will indicate which type of error we are facing in the code block after execution of stored procedure or trigger.

ERROR_NUMBER()  returns the error number.
ERROR_SEVERITY()   returns the error severity.
ERROR_STATE()  returns the error state number.
ERROR_LINE()  returns the error line number.
ERROR_PROCEDURE() returns the procedure or the trigger which causes error.
ERROR_MESSAGE()   returns the error message.

Result of above stored procedure


Few points to remember about try catch statements.

  1. TRY CATCH Statements must start with  BEGIN TRY and ends with END TRY 
  2. One or more T-SQL blocks can be specified into individual BEGIN TRY,BEGIN TRY blocks
  3. A try block must be followed immediately CATCH  block.
  4. Try block of one catch should not be in another try another statement block.
  5. Error severity more than 20 can causes connection closure to the database.Below 20 error functions can be handled by the try catch block
  6. Two types errors that are not handled by the try catch  1)syntax errors 2) Errors that occurs at statement level recompilation.