@@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.

@@ERROR Overview

Summary information:

Syntax	@@ERROR
On Success Returns	0
Return Data Type	INTEGER
New Batch	Does not reset the error code. See SQL Batches in SQL Server

Last Update: Microsoft SQL Server 2012.

@@ERROR Function Details

@@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.

@@ERROR Inside a Batch

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.

Non DML and DDL Statements Reset @@ERROR

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.

Resources

SQL Batches and GO Command

Microsoft SQL Server 2012 - Books Online

@@ERROR Function

SQLines Services

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