How to handle errors in SQL-SERVER {try…catch}

To implement error handling in your stored procedure in SQL-Server, we can user try…. catch syntax

BEGIN TRY
[your Transact-SQL]
END TRY
BEGIN CATCH
[your Transact-SQL]
END CATCH


To retrieve error information below are the built in functions in SQL-Server
ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

Example
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