SQL ERROR MESSAGES

SQL ERROR MESSAGES:

SQL server will raise an error when the code you have written cannot or should not execute. For example: You can’t create a table with same name in a database. The SQL Server will throw an error as below:

The components of SQL Server Error messages are as follows:

1)      Message Number: This is often referred as Error number. This is the message id of the error.

Message Number < 50000 – Are system supplied Errors.

Message Number > 50000 – Are User Defined Errors.

2)      Severity Level: Severity level is ranging from 0 to 25. The 1-10 levels are informative, where as 11 and higher are the actual errors. Anything over 20 is not good because it will terminate your connection from database.

3)      State: State is rarely used. If you raise the same error in several places, you can use this to mark the point at which the error was raised.

4)      Procedure: This can be the name of store procedure, function, trigger, or batch that contains the error.

5)      Line: The line number in the procedure, trigger, function or batch that contains the error.

6)      Message text: The actual message of the error which explains you what went wrong.

The Sys.Message table in the master contains the list of all error messages.

Custom Error Messages

You can create your own messages when in a particular situations. Suppose you have a store procedure “Test” in your database which takes EmpNo and Lastname as input and update the Employee’s last name into the table based on EmpNo which we have provided.

Let’s test our store proc as below:

Suppose one day you have passed a wrong EmpNo which does not exist into the table. At that time it will not throw an error rather than it will display a message as follows:

In this particular scenario we want to generate our custom message. We can achieve this by altering our SP(Store Proc) and adding some extra lines of code as follows:

ALTER PROCEDURE test @empid int,@lastname varchar(23)

AS

begin

UPDATE employee SET lastname= @lastname

WHERE empno=@empid

IF @@ROWCOUNT=0

RAISERROR('No Updates!!!!',16,1)

END

Now let’s run this code:

Now we are going to execute this store proc by giving a wrong employee number and we will see that what sql server gives in result.

Success!!!!!

SQL SERVER ERROR ACTIONS

There are four possible actions which SQL server will take when encounter errors:

A)    Statement termination- The statement with the procedure fails but the code keeps on running to the next statement. Transactions are not affected.

B)    Scope Abortion- The current procedure, function or batch is aborted and the next calling scope keeps running. I mean if SP A calls B and C, in case if B fails, then nothing in B runs but A continues to call C.

C)    Batch Abortion- The entire client call is terminated.

D)    Connection Termination- The client is disconnected and any open transaction is rolled back. This occurs when something really bad happens like a stack overflow or protocol error in the client library.