Search This Blog

Error Handling in SQL Server

Error Handling is one of the most important topics in SQL Server. The traditional way of handling errors in SQL Server is by checking the value of @@Error. When SQL Server completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. In case of an error, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

Here is an example.

1: create table TestTable(i int Primary key)
2: DECLARE @ErrCode int
4: insert into TestTable Select 1
6: insert into TestTable Select 1
7: IF @@Error<>0
8: SELECT @@Error, 'Error Occured'
10: insert into TestTable Select 1
11: SELECT @ErrCode = @@Error
12: IF @ErrCode <> 0
13: SELECT @ErrCode, 'Error Occured'

In the above example, we are creating a table with a single column ‘I’ as a primary key (line no 1). In the next line, I am declaring a variable to store @@ERROR values and I am trying to insert ‘1’ s into the table (line numbers 4,6,10). I didn’t put any error valuation for the fist insert, but it is always a good practice to do it for all your INSERT /UPDATE /DELETE Statements. Coming back to our example, again I am trying to insert a ‘1’ into the table, now as I have already inserted a ‘1’ into that primary key column, SQL server wont permits this insert, and so, @@ERROR won’t be zero. In the next line (#7), the if statement checks whether the value of @@Error is not equals to zero and since it is not zero, it will shows the current value of @@error and that error message. You can note from the second figure, that it is showing zero as the value of @@Error. So what happened here? As I mentioned earlier @@ERROR will contains the error value of its immediate statement, and the immediate of that SELECT @@Error statement is “IF @@Error<>0 ‘, since that statement is success, SQL resets the value of @@ERROR to zero and that’s why you are getting ‘0’ for SELECT @@Error. In order to avoid such confusions, better store the value of @@ERROR into a local variable and compare the value stored in this variable.

Sometimes the value @@ROWCOUNT also becomes very useful; as an example, consider a situation where I need to perform two insert operations on two different table; and the condition here is that I need to perform the second insert only if at lease one row has been inserted in the first. So the normal @@Error won’t be sufficient, you need to check the @@Error value also.

INSERT INTO FirstTable  SELECT <SomeValues>
IF @@ERROR<>0 OR @@Rowcount =0
GOTO OnError
INSERT INTO SecondTable SELECT <SomeValues>
IF @@ERROR<>0 OR @@Rowcount =0
GOTO OnError
Print 'Error'

No comments: