In this post, we will discuss various error functions in sql server 2008. Also, you can check my previous posts on:
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 the state of the raised error.
This function returns the 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 the error occurs.
Every error in sql server has severity how much severe the error is. This function will return the 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 a 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