@@ERROR (T-SQL)

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 the value 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 being 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 the 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 the 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 sysmessages
@@ROWCOUNT System Functions
SET @local_variable  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.