Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

@@ERROR

SQL Server 2000

Returns the error number for the last Transact-SQL statement executed.

Syntax

@@ERROR

Return Types

integer

Remarks

When Microsoft® SQL Server™ completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned. @@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

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.

Examples
A. Use @@ERROR to detect a specific error

This example uses @@ERROR to check for a check constraint violation (error #547) in an UPDATE statement.

USE pubs
GO
UPDATE authors SET au_id = '172 32 1176'
WHERE au_id = "172-32-1176"

IF @@ERROR = 547
   print "A check constraint violation occurred"
B. Use @@ERROR to conditionally exit a procedure

The IF...ELSE statements in this example test @@ERROR after an INSERT statement in a stored procedure. The value of the @@ERROR variable determines the return code sent to the calling program, indicating success or failure of the procedure.

USE pubs
GO

-- Create the procedure.
CREATE PROCEDURE add_author 
@au_id varchar(11),@au_lname varchar(40),
@au_fname varchar(20),@phone char(12),
@address varchar(40) = NULL,@city varchar(20) = NULL,
@state char(2) = NULL,@zip char(5) = NULL,
@contract bit = NULL
AS

-- Execute the INSERT statement.
INSERT INTO authors
(au_id,  au_lname, au_fname, phone, address, 
 city, state, zip, contract) values
(@au_id,@au_lname,@au_fname,@phone,@address,
 @city,@state,@zip,@contract)

-- Test the error value.
IF @@ERROR <> 0 
BEGIN
   -- Return 99 to the calling program to indicate failure.
   PRINT "An error occurred loading the new author information"
   RETURN(99)
END
ELSE
BEGIN
   -- Return 0 to the calling program to indicate success.
   PRINT "The new author information has been loaded"
   RETURN(0)
END
GO
C. Use @@ERROR to check the success of several statements

This example depends on the successful operation of the INSERT and DELETE statements. Local variables are set to the value of @@ERROR after both statements and are used in a shared error-handling routine for the operation.

USE pubs
GO
DECLARE @del_error int, @ins_error int
-- Start a transaction.
BEGIN TRAN

-- Execute the DELETE statement.
DELETE authors
WHERE au_id = '409-56-7088'

-- Set a variable to the error value for 
-- the DELETE statement.
SELECT @del_error = @@ERROR

-- Execute the INSERT statement.
INSERT authors
   VALUES('409-56-7008', 'Bennet', 'Abraham', '415 658-9932',
   '6223 Bateman St.', 'Berkeley', 'CA', '94705', 1)
-- Set a variable to the error value for 
-- the INSERT statement.
SELECT @ins_error = @@ERROR

-- Test the error values.
IF @del_error = 0 AND @ins_error = 0
BEGIN
   -- Success. Commit the transaction.
   PRINT "The author information has been replaced"    
   COMMIT TRAN
END
ELSE
BEGIN
   -- An error occurred. Indicate which operation(s) failed
   -- and roll back the transaction.
   IF @del_error <> 0 
      PRINT "An error occurred during execution of the DELETE 
      statement." 

   IF @ins_error <> 0
      PRINT "An error occurred during execution of the INSERT 
      statement." 

   ROLLBACK TRAN
END
GO
D. Use @@ERROR with @@ROWCOUNT

This example uses @@ERROR with @@ROWCOUNT to validate the operation of an UPDATE statement. The value of @@ERROR is checked for any indication of an error, and @@ROWCOUNT is used to ensure that the update was successfully applied to a row in the table.

USE pubs
GO
CREATE PROCEDURE change_publisher
@title_id tid, 
@new_pub_id char(4) 
AS

-- Declare variables used in error checking.
DECLARE @error_var int, @rowcount_var int

-- Execute the UPDATE statement.
UPDATE titles SET pub_id = @new_pub_id 
WHERE title_id = @title_id 

-- Save the @@ERROR and @@ROWCOUNT values in local 
-- variables before they are cleared.
SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT

-- Check for errors. If an invalid @new_pub_id was specified
-- the UPDATE statement returns a foreign-key violation error #547.
IF @error_var <> 0
BEGIN
   IF @error_var = 547
   BEGIN
      PRINT "ERROR: Invalid ID specified for new publisher"
      RETURN(1)
   END
   ELSE
   BEGIN
      PRINT "ERROR: Unhandled error occurred"
      RETURN(2)
   END
END

-- Check the rowcount. @rowcount_var is set to 0 
-- if an invalid @title_id was specified.
IF @rowcount_var = 0 
BEGIN
   PRINT "Warning: The title_id specified is not valid"
   RETURN(1)
END
ELSE
BEGIN
   PRINT "The book has been updated with the new publisher"
   RETURN(0)
END
GO

See Also

Error Handling

@@ROWCOUNT

SET @local_variable

sysmessages

System Functions

Show:
© 2014 Microsoft