Error Handling (Transact-SQL)

This section covers error handling mechanisms in Transact-SQL (T-SQL), the procedural extension of SQL used by Microsoft SQL Server.

Introduction

Robust error handling is crucial for building reliable and maintainable SQL Server applications. T-SQL provides several constructs to detect, manage, and respond to errors that occur during query execution.

TRY...CATCH Blocks

The TRY...CATCH statement provides structured exception handling in T-SQL. Code that might cause an error is placed in the TRY block. If an error occurs within the TRY block, execution is immediately transferred to the CATCH block.

BEGIN TRY
    -- Code that might raise an error
    SELECT 1 / 0;
END TRY
BEGIN CATCH
    -- Error handling logic
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_SEVERITY() AS ErrorSeverity,
        ERROR_STATE() AS ErrorState,
        ERROR_PROCEDURE() AS ErrorProcedure,
        ERROR_LINE() AS ErrorLine,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

ERROR_NUMBER()

Returns the error number.

ERROR_SEVERITY()

Returns the severity level of the error.

ERROR_STATE()

Returns the state number of the error.

ERROR_PROCEDURE()

Returns the name of the stored procedure or trigger where the error occurred.

ERROR_LINE()

Returns the line number of the statement that caused the error.

ERROR_MESSAGE()

Returns the full text of the error message.

@@ERROR

The global variable @@ERROR returns the error number of the last Transact-SQL statement that executed. If the statement executed successfully, @@ERROR returns 0. If it failed, it returns the error number.

Note: While @@ERROR is still supported, it is recommended to use TRY...CATCH for more modern and comprehensive error handling.

-- Example using @@ERROR (older method)
INSERT INTO MyTable (Column1) VALUES ('Some Value');
IF @@ERROR <> 0
BEGIN
    PRINT 'Error inserting data.';
    -- Handle the error
END;

RAISERROR

The RAISERROR statement is used to raise a user-defined error or to return error information from a stored procedure or trigger.

-- Raising a severity 16 error
RAISERROR('Invalid input value provided.', 16, 1);

-- Raising a custom message with severity and state
RAISERROR('The requested item %s was not found. Code: %d', 10, 1, 'Product ABC', 1001) WITH LOG;

Severity Levels

THROW

Introduced in SQL Server 2012, THROW is a simpler way to raise an error or re-throw an error from a CATCH block. It requires specifying an error number, message, and severity.

BEGIN TRY
    -- Some operation
    IF EXISTS (SELECT * FROM AnotherTable WHERE ID = @InputID)
    BEGIN
        -- Simulate an error
        THROW 51000, 'The ID already exists.', 1;
    END
END TRY
BEGIN CATCH
    -- Re-throwing the caught error
    THROW;
END CATCH;

SET NOCOUNT ON

It's good practice to use SET NOCOUNT ON at the beginning of stored procedures and triggers. This prevents the message showing the number of rows affected by a statement from being returned. This reduces network traffic and can prevent issues with client applications that misinterpret these messages as data.

CREATE PROCEDURE MyProc
AS
BEGIN
    SET NOCOUNT ON;

    -- Procedure logic
END
Best Practice: Always include SET NOCOUNT ON in your stored procedures and triggers.

Common Error Handling Scenarios

Further Reading