Home > SQL Server > Error Handling in SQL Server 2008 R2–Questions Answered

Error Handling in SQL Server 2008 R2–Questions Answered


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

  1. BEGIN TRY
  2. BEGIN TRANSACTION
  3. /* Insert or Update or Delete statements */
  4. COMMIT TRANSACTION
  5. END TRY
  6. BEGIN CATCH
  7. INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease)
  8. SELECT ERROR_NUMBER() AS ErrorNumber,
  9. ERROR_SEVERITY() AS ErrorSeverity,
  10. ERROR_STATE() as ErrorState,
  11. ERROR_LINE () as ErrorLine,
  12. ERROR_PROCEDURE() as ErrorProcedure,
  13. ERROR_MESSAGE() as ErrorMessage
  14. IF (XACT_STATE()) = -1
  15. BEGIN
  16. ROLLBACK TRAN
  17. END
  18. ELSE IF (XACT_STATE()) = 1
  19. BEGIN
  20. COMMIT TRAN
  21. END
  22. RAISEERROR(‘Application Error Occurred. Please contact administrator’, 16, -1)
  23. 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

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: