Microsoft® SQL Server® 2012 Bible

413

Chapter 16: Programming with T-SQL

16

The basic error information system functions, such as @@error and @@rowcount, contain

the status for the previous T-SQL command in the code. This means that the legacy method

of error handling must examine T-SQL’s system functions and handle the error after each

SQL statement that might potentially encounter an error.

@@error System Function

The @@error system function contains the integer error code for the previous T-SQL state-

ment. A 0 indicates success.

The diffi culty is that @@error, unlike other languages that hold the last error in a variable

until another error occurs, is updated for every command, so even testing its value updates it.

The following code sample attempts to update the primary key to a value, but it has a for-

eign key constraint on it. This generates an error. The @ErrorMsg parameter is set to

@@error, and then @@error and @ErrorMsg are both sent to PRINT commands. The sec-

ond PRINT for @ErrorNumber contains the error number, but the fi rst PRINT command

returns 0. This is because the successful SET command cleared the @@Error value.

DECLARE @ErrorNumber nvarchar(1000);

UPDATE HumanResources.Employee

SET BusinessEntityID = 30000

WHERE BusinessEntityID = 2;

SET @ErrorNumber = @@Error

PRINT @@error;

PRINT @ErrorNumber;

Result:

Msg 547, Level 16, State 0, Line 3

The UPDATE statement conflicted with the REFERENCE constraint

"FK_EmployeeDepartmentHistory_Employee_BusinessEntityID". The

conflict occurred in database "AdventureWorks2012", table

"HumanResources.EmployeeDepartmentHistory", column 'BusinessEntityID'.

The statement has been terminated.

0

547

This example illustrates both the problem and solution for @@error. If you want to capture

this value, set it to a local variable immediately after the statement you want to capture

the error for.

@@rowcount System Function

Another way to determine whether the query was a success is to check the number of rows

affected. Even if no error was generated, it’s possible that the data didn’t match and the

c16.indd 413c16.indd 413 7/30/2012 5:38:10 PM7/30/2012 5:38:10 PM

http://www.it-ebooks.info