See Also: Main_Page - Transact SQL Language Elements - Error Handling

Error handling functions allow you to retrieve information about the error. Once you have error metadata you can decide to stop the execution of the code module, continue with an alternative logical branch of the code module, record the error in the Application Log and so forth.

Contents

@@ERROR

The @@ERROR function returns the number of the last error encountered on the current connection. If there are no errors, @@ERROR returns 0. With SQL Server 2000 and previous releases checking @@ERROR was the only way to diagnose and troubleshoot errors. SQL Server 2005 introduces TRY / CATCH syntax for error handling as well as several new functions.

Note that @@ERROR returns the error number returned by the last executed statement, so it's important to catch the error immediately after it occurs. For example, the following query catches the error because it checks for the error immediately after the statement that encountered the error:

SELECT 1 / 0  SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      -----------------------------------------------  error number is: 8134

However, the next example does NOT catch the error because it checks the @@ERROR function value too late, after a statement that completes successfully:

SELECT 1 / 0  SELECT 'this is a successfull statement. it resets @@ERROR to zero!'  SELECT 'error number is: ' + CAST(@@ERROR AS VARCHAR)

Results:

-----------  Msg 8134, Level 16, State 1, Line 1  Divide by zero error encountered.      -----------------------------------------------------------  this is a successfull statement. it resets @@ERROR to zero!      -----------------------------------------------  error number is: 0

@@ROWCOUNT

The @@ROWCOUNT function returns the number of rows affected by the last query. This function can be used effectively to find out whether the number of rows modified is the same as what you intended to modify. If your query was supposed to update 15 rows but @@ROWCOUNT returns 10 then something must have gone wrong. Much like with @@ERROR it's important to get the @@ROWCOUNT value immediately after the statement you want to examine. For example, the following query erroneously reports that the total number of affected rows is one, even though your main query returned 10 rows, as desired:

SELECT TOP 10 * FROM dimCustomer  SELECT 'this is a successfull statement. it resets @@ROWCOUNT to one!'  SELECT 'number of rows affected is: ' + CAST(@@ROWCOUNT AS VARCHAR)

ROWCOUNT_BIG()

The ROWCOUNT_BIG() function does the same thing as @@ROWCOUNT, but returns a BIGINT data type instead of an INT data type.

ERROR_LINE

The ERROR_LINE function returns the line number at which the error occurred which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the line number where the error occurred:

BEGIN TRY  SELECT 'empty string'  SELECT 1 / 0  END TRY    BEGIN CATCH  SELECT 'the error occured at line ' + CAST(ERROR_LINE() AS VARCHAR)  END CATCH

Results:

------------  empty string      -----------      --------------------------------------------------------  the error occured at line 3

ERROR_MESSAGE

The ERROR_MESSAGE function returns the text of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error text:

BEGIN TRY  SELECT 1 / 0  END TRY    BEGIN CATCH  SELECT 'the error was: ' + ERROR_MESSAGE()  END CATCH

Results:

-----------      --------------------------------------------------  the error was: Divide by zero error encountered.

ERROR_NUMBER

The ERROR_NUMBER function returns the number of the error which caused the CATCH block of TRY / CATCH logic to execute. This function can be very useful in determining the statement that caused the error and troubleshooting the code module (stored procedure) that encountered the error. The function does not accept any parameters. For example, the following query returns the error number:

BEGIN TRY  SELECT 1 / 0  END TRY    BEGIN CATCH  SELECT 'the error number was: ' + CAST(ERROR_NUMBER() AS VARCHAR)  END CATCH

Results:

-----------      --------------------------------------------------  the error number was: 8134

ERROR_PROCEDURE

The ERROR_PROCEDURE function returns the name of the stored procedure or trigger that encountered the error. This function does not accept any parameters and can be effectively called from CATCH block. For example, the following query creates a stored procedure that intentionally causes divide by zero error. Next the procedure is executed and the name of the erroneous stored procedure is returned:

CREATE PROCEDURE my_test_proc  AS    SELECT 1 / 0  GO    BEGIN TRY  EXEC my_test_proc  END TRY    BEGIN CATCH  SELECT 'the erroneous procedure was: ' + ERROR_PROCEDURE()  END CATCH

Results:

------------------------------------------  the erroneous procedure was: my_test_proc

ERROR_SEVERITY

The ERROR_SEVERITY function returns the severity of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error severity:

BEGIN TRY  SELECT 1 / 0  END TRY    BEGIN CATCH  SELECT 'the error severity was: ' + CAST(ERROR_SEVERITY() AS VARCHAR)  END CATCH

Results:

------------------------------------------------------  the error severity was: 16

ERROR_STATE

The ERROR_STATE function returns the state of the error which caused the CATCH block of TRY / CATCH logic to execute. The function does not accept any parameters. For example, the following query returns the error state:

BEGIN TRY  SELECT 1 / 0  END TRY    BEGIN CATCH  SELECT 'the error state was: ' + CAST(ERROR_STATE() AS VARCHAR)  END CATCH

Results:

------------------------------------------------------  the error state was: 1

==@@TRANCOUNT

The @@TRANCOUNT function returns the number of open transactions on a particular connection. If @@TRANCOUNT returns anything other than 0, something must have gone wrong and at least one transaction has not been committed on the current connection. This function does not take any parameters. For example, the following query does not commit a transaction, therefore @@TRANCOUNT returns 1:

BEGIN TRAN   UPDATE dimOrganization   SET OrganizationName = 'test'   WHERE OrganizationName = 'new organization'    SELECT @@TRANCOUNT

Result:

-----------  1

Recall that the ROLLBACK TRANSACTION statement closes all open transactions and "undoes" all work performed on the current connection. On the other hand, COMMIT TRANSACTION only commits one transaction at a time. Therefore if @@TRANCOUNT returns a value of 3 and you execute ROLLBACK the transaction count will return to zero; if you execute COMMIT, however, the transaction count will be set to 2 and only the innermost transaction will be saved. Please refer to "Transactions and Locking" section for more information regarding transactions.

XACT_STATE

The XACT_STATE function is new with SQL Server 2005. It is similar to @@TRANCOUNT since it determines whether there is an uncommitted transaction on the current connection. The XACT_STATE function does not accept any parameters. Unlike @@TRANCOUNT the XACT_STATE function can also determine if the uncommitted transaction has been classified as an un-committable transaction. Note however, that XACT_STATE function cannot be used to determine if there are multiple (nested) transactions open on the current connection. This function can return one of the following values:

  • 1 The session has an active transaction. The transaction can be committed.
     
  • 0 The session has NO active transactions.
     
  • (-1) The session has an active transaction; an error has occurred which classifies the active transaction as un-committable. The transaction cannot be committed; neither can the session request rolling back to a savepoint. Rather the entire transaction must be rolled back. After the transaction has been rolled back the session can initiate a new transaction.

You can effectively use XACT_STATE function for error handling, as shown below:

IF (XACT_STATE()) = -1      BEGIN  PRINT 'The transaction is in an uncommittable state. Rolling back transaction.'          ROLLBACK TRANSACTION;      END

A transaction may become un-committable within a TRY block of the TRY / CATCH construct if an error occurs that would have otherwise terminated the transaction. For example, any errors in CREATE TABLE statement executed within a TRY block would cause the transaction to become un-committable; outside of a TRY block such transactions would simply be terminated. For example, the following query attempts to drop a column that does not exist in the 'test' table, without TRY / CATCH this transaction is rolled back:

BEGIN TRAN  ALTER TABLE test DROP COLUMN test_ident

Results:

Msg 4924, Level 16, State 1, Line 2  ALTER TABLE DROP COLUMN failed because column 'test_ident' does not exist in table 'test'.

The same statement causes the transaction to become un-committable when used with TRY / CATCH:

BEGIN TRAN  BEGIN TRY  ALTER TABLE test DROP COLUMN test_ident  END TRY  BEGIN CATCH  SELECT ERROR_NUMBER() AS error_number,     XACT_STATE() AS transaction_state   SELECT ERROR_MESSAGE()  END CATCH

Results:

error_number transaction_state  ------------ -----------------  4924         -1      ----------------------------------------------------------------------------------------  ALTER TABLE DROP COLUMN failed because column 'test_ident' does not exist in table 'test'.

If the transaction is in un-committable state the connection cannot initiate any other transactions and cannot write to the transaction log. If you attempt to execute any other transactions on the same connection SQL Server will return an error advising you to rollback the open transaction, as follows:

BEGIN TRAN  BEGIN TRY  ALTER TABLE test DROP COLUMN test_ident  END TRY  BEGIN CATCH   SELECT ERROR_NUMBER() AS error_number,     XACT_STATE() AS transaction_state   SELECT ERROR_MESSAGE()  END CATCH  -- attempt to execute another, implicit transaction:  DROP TABLE test

Results:

error_number transaction_state  ------------ -----------------  4924         -1      ----------------------------------------------------------------------------------------  ALTER TABLE DROP COLUMN failed because column 'test_ident' does not exist in table 'test'.    Msg 3930, Level 16, State 1, Line 13  The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.