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
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.
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
No comments:
Post a comment