How to handle errors(Exception Handling) in SQl server?

Exceptional Handling In SQL Server:

Error or exception handling in sql server is easy task. In Sql server we can find the error using TRY CATCH statement, which helps us to find the error effectively in the back end. Exceptional handling provides additional information about the errors such as Error Message, Error Line, Error Number etc.

We put Sql statement in TRY block and to handle error we write code in CATCH block. If error occurs in try block then control automatically goes to CATCH block.

Syntax of TRY CTACH:

BEGIN TRY

--T-SQL statements

--or T-SQL statement blocks

END TRY

BEGIN CATCH

--T-SQL statements

--or T-SQL statement blocks

END CATCH

Error Functions used within CATCH block

1.      ERROR_NUMBER():     This returns the error number.

2.      ERROR_LINE(): Returns line no of SQL statement that caused error.

3.      ERROR_SEVERITY(): Returns the severity level of the error.

4.      ERROR_STATE(): Returns the state number of the error.

5.      ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error occurred.

6.      ERROR_MESSAGE():Returns the full text of error message.

Example of Exceptions Handling:

BEGIN TRY

DECLARE @num INT

SET @num = 5/0

PRINT 'This will not execute'

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER() AS ErrNumber, ERROR_SEVERITY() AS ErrSeverity, ERROR_STATE() AS ErrState, ERROR_PROCEDURE() AS ErrProcedure,

 ERROR_LINE() AS ErrLine, ERROR_MESSAGE() AS ErrMessage;

END CATCH;

GO

Result: