Share via

  • Article

Retrieving Error Information in Transact-SQL

There are two ways to obtain error information in Transact-SQL:

  • Within the scope of the CATCH block of a TRY…CATCH construct, you can use the following system functions:

    • ERROR_LINE() returns the line number at which the error occurred.

    • ERROR_MESSAGE() returns the text of the message that would be returned to the application. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.

    • ERROR_NUMBER() returns the error number.

    • ERROR_PROCEDURE() returns the name of the stored procedure or trigger in which the error occurred. This function returns NULL if the error did not occur inside a stored procedure or trigger.

    • ERROR_SEVERITY() returns the severity.

    • ERROR_STATE() returns the state.

  • Immediately after executing any Transact-SQL statement, you can test for an error and retrieve the error number using the @@ERROR function.

Using ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE

The ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE functions only return error information when they are used within the scope of the CATCH block of a TRY…CATCH construct. Outside the scope of a CATCH block they return NULL. These functions return information about the error that caused the CATCH block to be invoked. The functions return the same error information anywhere they are run within the scope of a CATCH block, even if they are referenced multiple times. The functions provide to Transact-SQL statements the same data that is returned to the application.

In nested CATCH blocks, the ERROR_LINE, ERROR_MESSAGE, ERROR_NUMBER, ERROR_PROCEDURE, ERROR_SEVERITY, and ERROR_STATE functions return the error information specific to the CATCH block in which they are referenced. For example, the CATCH block of an outer TRY...CATCH construct could have a nested TRY...CATCH construct. Within the nested CATCH block, these functions return information about the error that invoked the inner CATCH block. The same functions in the outer CATCH block would return information about the error that invoked that CATCH block.

The following example illustrates this by showing that when ERROR_MESSAGE is referenced in the outer CATCH block, it returns the message text generated by the outer TRY block. When referenced in the inner CATCH block, ERROR_MESSAGE returns the text generated in the inner TRY block. The example also illustrates that in the outer CATCH block ERROR_MESSAGE always returns the message generated in the outer TRY block, even after the inner TRY...CATCH construct has been run.

IF EXISTS (SELECT message_id FROM sys.messages
    WHERE message_id = 50010)
        EXECUTE sp_dropmessage 50010;
GO
EXECUTE sp_addmessage @msgnum = 50010,
    @severity = 16, 
    @msgtext = N'Message text is from the %s TRY block.';
GO
BEGIN TRY -- Outer TRY block.
    -- Raise an error in the outer TRY block.
    RAISERROR (50010, -- Message id.
        16, -- Severity,
        1, -- State,
        N'outer'); -- Indicate TRY block.
END TRY -- Outer TRY block.
BEGIN CATCH -- Outer CATCH block.
    -- Print the error message recieved for this
    -- CATCH block.
    PRINT N'OUTER CATCH1: ' + ERROR_MESSAGE();
    BEGIN TRY -- Inner TRY block.
        -- Start a nested TRY...CATCH and generate
        -- a new error.
        RAISERROR (50010, -- Message id.
            16, -- Severity,
            2, -- State,
            N'inner'); -- Indicate TRY block.
    END TRY -- Inner TRY block.
    BEGIN CATCH -- Inner CATCH block.
        -- Print the error message recieved for this
        -- CATCH block.
        PRINT N'INNER CATCH: ' + ERROR_MESSAGE();
    END CATCH; -- Inner CATCH block.
    -- Show that ERROR_MESSAGE in the outer CATCH
    -- block still returns the message from the
    -- error generated in the outer TRY block.
    PRINT N'OUTER CATCH2: ' + ERROR_MESSAGE();
END CATCH; -- Outer CATCH block.
GO

Retrieving Information Using @@ERROR

The @@ERROR function can be used to capture the number of an error generated by the previous Transact-SQL statement. @@ERROR only returns error information immediately after the Transact-SQL statement that generates the error.

  • If the statement generating the error is in a TRY block, the @@ERROR value must be tested and retrieved in the first statement in the associated CATCH block.

  • If the statement generating the error is not in a TRY block, the @@ERROR value must be tested and retrieved in the statement immediately after the one generating the error.

Outside the scope of a CATCH block, the error number in @@ERROR is the only information available about an error within Transact-SQL code. If the error used an error message defined in sys.messages, you can retrieve the defined severity and error message text from sys.messages as illustrated in this example.

IF EXISTS (SELECT message_id FROM sys.messages
    WHERE message_id = 50010)
        EXECUTE sp_dropmessage 50010;
GO
-- Define a message with text that accepts
-- a substitution string.
EXECUTE sp_addmessage @msgnum = 50010,
    @severity = 16, 
    @msgtext = N'Substitution string = %s.';
GO
DECLARE @ErrorVariable INT;
-- RAISERROR uses a different severity and
-- supplies a substitution argument.
RAISERROR (50010, -- Message id.
    15, -- Severity,
    1, -- State,
    N'ABC'); -- Substitution Value.
-- Save @@ERROR.
SET @ErrorVariable = @@ERROR;
-- The results of this select illustrate that
-- outside a CATCH block only the original
-- information from sys.messages is available to
-- Transact-SQL statements. The actual message
-- string returned to the application is not
-- available to Transact-SQL statements outside
-- of a CATCH block.
SELECT @ErrorVariable AS ErrorID,
    text
FROM sys.messages
WHERE message_id = @ErrorVariable;
GO