Powered by Blogger.

Saturday, February 15, 2014

Error functions in sql server

In this post we will discuss about various error functions in sql server 2008. Also you can check my previous posts on:

- Exception Class in C#.Net

- Get weekday name in sql server 2008

- Serialization and Deserialization in Remoting in C#.Net

Sql server provides a set of functions to get information about raised errors which are known as Error Functions.

Within the scope of a TRY CATCH block, you can use the following error  functions:

Every error in sql server is associated with a unique number which is uniquely identified. This function returns the error number of the currently raised error in your program.

Every error in sql server is associated with a state which can be used to identify exact location in the program where that error occurs. This function will return state of the raised error.

This function returns name of the stored procedure or trigger in which error occurs. This function returns NULL if the error did not occur inside a stored procedure or trigger.

This function is used to get the message related to the raised error in the program. This function returns the text of the message that would be returned to the application. The text includes the values supplied for any substitutable parameters such as names, length or time.

This function is used to get the line number with in the stored sub program where error occurs.

Every error in sql server has severity how much severe the error is. This function will return severity of the currently raised error in the program.

Immediately after executing any TSQL statement, you can test for an error and retrieve that error number using the @@ERROR function.

Getting list of error messages:
By using a system table   called sys.messages you can get the complete list of error messages available in the sql server to which you are connected.
Ex:  Select * from sys.messages

By using the stored procedure named sp_addmessage you can add your own messages to the built in error messages of sql server.
Ex.  Sp_addmessage msgid, severity, msgtext