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
3:
4: insert into TestTable Select 1
5:
6: insert into TestTable Select 1
7: IF @@Error<>0
8: SELECT @@Error, 'Error Occured'
9:
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
RETURN
OnError:
Print 'Error'
