With reference to the Question posted by Hima Bindu Vejella on http://himabinduvejella.blogspot.com/2011/06/how-did-you-handle-errors-in-sqlserver.html, I have posted these answers, I hope you all find this usefull
How did you handle errors in SqlServer 2008 R2?
We handle errors using
- BEGIN TRY
- BEGIN TRANSACTION
- /* Insert or Update or Delete statements */
- COMMIT TRANSACTION
- END TRY
- BEGIN CATCH
- INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease)
- SELECT ERROR_NUMBER() AS ErrorNumber,
- ERROR_SEVERITY() AS ErrorSeverity,
- ERROR_STATE() as ErrorState,
- ERROR_LINE () as ErrorLine,
- ERROR_PROCEDURE() as ErrorProcedure,
- ERROR_MESSAGE() as ErrorMessage
- IF (XACT_STATE()) = -1
- BEGIN
- ROLLBACK TRAN
- END
- ELSE IF (XACT_STATE()) = 1
- BEGIN
- COMMIT TRAN
- END
- RAISEERROR(Application Error Occurred. Please contact administrator, 16, -1)
- END CATCH()
What are the different ways of handling errors in SQL Server?
1. We can handle errors by checking @@ERROR, if it’s not equal to 0, then we can use RAISERRROR to return error message to application
2. We can handle error by checking @@ERROR and @@ROWCOUNT in combination and then use RAISERROR or RETURN to return error message or code to application
3. We can use TRY..CATCH method
Which is the optimized way to handle errors?
The optimized way to handle errors is to create Error Log table with following columns (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, UserName, HostName, ErrorDateTime, ApplicationName) and then create a Stored Procedure to get these columns as Input Parameter except ErrorDateTime, for which we can use GetDate() and then call this Stored Procedure inside BEGIN CATCH..END CATCH block
What are the Built-In Objects available related to error handling?
The following are the built in objects related to Error Handling and Information
ERROR_NUMBER() – Returns Error Number of the Error that caused the CATCH block of TRY…CATCH to run
ERROR_SEVERITY() – Returns severity of Error that caused the CATCH block of TRY…CATCH to run
ERROR_STATE() – Returns State Number of the Error that caused the CATCH block of TRY…CATCH to run
ERROR_LINE () – Returns the line number at which an error occurred that caused the CATCH block of TRY…CATCH to run
ERROR_PROCEDURE() – Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.
ERROR_MESSAGE() – Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run
@@ERROR – Returns the Error number of the last T-SQL Statement run
RAISERROR – Generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY…CATCH construct.
Sys.Messages – This is a Catalog view which contains the list of system defined and user defined messages
SP_AddMessage – To define a new user-defined error message in a SQL Server instance
sp_dropmessage – To drop a specified user-defined error message from a SQL Server instance
xp_logevent – Logs a user defined message in SQL Server log file and in the Windows Event Viewer. It can be used to send an alert in the background to administrators without showing a message to client
Print – To simply display the message, we can use PRINT statement followed with Error message
How can return Error information to Application ?
We can return error information back to application using RAISERROR Or RETURN
RAISERROR (‘Application Error Occurred’, 16, -1)
RETURN -1 –In Application we have to check for Return value and display corresponding message
SQLXpertise 