Handling SqlServer Error

How to handle SqlServer Error in SqlServer2005 or higher version..?

To handle error in SqlServer we can use Try..Catch like below



BEGIN TRY
   'Your Sql Query'
END TRY
BEGIN CATCH
   PRINT 'An error occurred'
END CATCH

If you want to know full information about the Error then you use some function in CATCH block like...


ERROR_NUMBER()
ERROR_SEVERITY()
ERROR_STATE()
ERROR_PROCEDURE()
ERROR_LINE()
ERROR_MESSAGE()

You can select these function value to know full information about an error. The meaning of these function are given below...

•  ERROR_NUMBER.  The number of the error that occurred. 
• ERROR_MESSAGE.  The complete text of the error message.
• ERROR_LINE.  This is the line number of the batch or stored procedure where the  
                  error occured.
• ERROR_SEVERITY.  This is the severity of the error.  The CATCH block only fires for errors with severity 11 or higher.  Error severities from 11 to 16 are typically user or code errors. Severity levels from 17 to 25 are usually software or hardware errors where processing may not be able to continue.
• ERROR_STATE.  This is sometimes used by the system to return more information about 
                     the error.
• ERROR_PROCEDURE.  If you are in a Stored Procedure then this will return the name of
                             the procedure.