Latest Post

Saturday, 24 September 2016

How to use TRY CATCH in sql server



TRY…CATCH and ERROR Handling

TRY... CATCH is used to separate logic and error handling code. This is introduced in SQL Server 2005. The logical code is enclosed in the TRY block and error handling code is enclosed in the CATCH block. if the code or logic written in try block fails then it will move to catch block to handle the error and provide error information like ID, text, message, state etc.

   BEGIN TRY
      DECLARE @Number INT
      SELECT @Number = 1/0
      PRINT 'Not reached'
   END TRY
   BEGIN CATCH
      PRINT 'This is the error: ' + error_message();
   END CATCH

Following are the functions that can be used in CATCH block  :

ERROR_NUMBER(): This will give the error number.
ERROR_SEVERITY(): This will give the severity level of the error.
ERROR_STATE(): This will give state number of the error.
ERROR_LINE(): This will give the error line number.
ERROR_PROCEDURE():This will give the name of the stored procedure that occurred the error.
ERROR_MESSAGE(): This will give the full message text of the error.

BEGIN TRY
-- Generate a divide-by-zero error.
DECLARE @Number INT=50
SELECT @Number/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;
GO



Government Jobs