What is error handling in stored procedures of SQL Server 2008?

 Posted by Tripati_tutu on 10/20/2010 | Category: Sql Server Interview questions | Views: 4991 | Points: 40
Answer:

In previous versions of SQL Server you would handle exceptions by checking the @@error global variable immediately after an INSERT, UPDATE or DELETE, and then perform some corrective action if @@error did not equal zero.

SQL Server 2005 provides structured exception handing through TRY CATCH block as other programming language like JAVA, C# etc.

Example:
BEGIN TRY 

RAISERROR ('A problem is raised', 16,1)
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() as ERROR_NUMBER,
ERROR_SEVERITY() as ERROR_SEVERITY,
ERROR_STATE() as ERROR_STATE,
ERROR_MESSAGE() as ERROR_MESSAGE
END CATCH


ERROR_NUMBER() returns the number of the errors.
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 and times etc.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Rajesh.Dharmakkan on: 12/9/2010 | Points: 10
Excellent article.
It used to my on time whenever i search the same.

Login to post response