@@ERROR - Get Last Error Code - SQL Server for Oracle DBAs and Developers

In SQL Server, @@ERROR function returns the error code of the last Transact-SQL statement executed.

Note that even IF and PRINT statements reset the error code, so you have to check it immediately after executing a DML or DDL statement, or save it to a local variable and check later.

Summary information:

Last Update: Microsoft SQL Server 2012.

@@ERROR function returns the error code of the last Transact-SQL statement, or 0 on successful execution:

SQL Server:

  -- Create the table for the first time
  CREATE TABLE cities (name VARCHAR(90))
  GO
 
  -- No errors, 0 is printed
  PRINT @@ERROR
 
  -- Table already exists
  CREATE TABLE cities (name VARCHAR(90))
  GO
 
  -- Error code 2714 is printed
  PRINT @@ERROR
 
  # 0
  # Msg 2714, Level 16, State 6, Line 4
  # There is already an object named 'cities' in the database.
  # 2714

Note that GO is not a Transact-SQL statement, and GO acts as the SQL batch separator in SQLCMD utility (analog of Oracle SQL*Plus) or SQL Server Management Studio's Query window. For more information, see SQL Batches in SQL Server.

Be careful when you use @@ERROR inside a batch as syntax and some run-time errors (CREATE TABLE i.e.) terminate the batch execution:

SQL Server:

  -- Table already exists
  CREATE TABLE cities (name VARCHAR(90))
 
  -- Try to print message in the same batch (it will never be executed if there is an error in CREATE TABLE)
  PRINT 'Attempt 1 - Error: ' + CAST(@@ERROR AS VARCHAR)
  GO
 
  -- Table already exists
  CREATE TABLE cities (name VARCHAR(90))
  GO
 
  -- Now PRINT is in a separate batch and message will be printed
  PRINT 'Attempt 2 - Error: ' + CAST(@@ERROR AS VARCHAR)
  GO
 
  # Msg 2714, Level 16, State 6, Line 2
  # There is already an object named 'cities' in the database.
  # Msg 2714, Level 16, State 6, Line 3
  # There is already an object named 'cities' in the database.
  # Attempt 2 - Error: 2714

You can see that the error in the first CREATE TABLE terminated the batch, so PRINT statement located in the same batch and referencing @@ERROR was not executed.

At the same time, the second PRINT is in a separate batch, so the error code was printed. For more information about error handling in batches, see SQL Batches in SQL Server.

Also note that any Transact-SQL statement resets the error:

SQL Server:

  CREATE TABLE cities (name VARCHAR(90))
  GO
 
  PRINT 'Error code: '
  PRINT @@ERROR
 
  # Msg 2714, Level 16, State 6, Line 1
  # There is already an object named 'cities' in the database.
  # Error code: 
  # 0

You can see that although CREATE TABLE raised the error, the first PRINT statement reset it, so the second PRINT statement printed 0.

• SQL Server for Oracle DBAs and Developers
• Oracle to SQL Server Migration Knowledge Base

• SQL Batches and GO Command

Microsoft SQL Server 2012 - Books Online

• @@ERROR Function

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.