Error handling in Transact-SQL (T-SQL)

Error handling in Transact-SQL (T-SQL) is crucial for managing unexpected issues that may arise during the execution of SQL statements.

TRY-CATCH Block

This is the primary mechanism for handling errors in T-SQL. It functions similarly to exception handling in languages like C#. It allows us to enclose a block of T-SQL statements within a TRY block and then specify one or more CATCH blocks to handle different types of errors that may occur within the TRY block.

BEGIN TRY
    -- T-SQL statements that may cause errors
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

RAISE ERROR

This function can be used to raise a custom error message or exception within the T-SQL code. It allows you to specify an error number, error message, and severity level.

RAISEERROR('Error Message', 20, 1);

Error Functions

T-SQL provides several functions to retrieve information about errors that occur. Some commonly used functions are given below.

ERROR_NUMBER(): Returns the number of the error.
ERROR_MESSAGE(): Provides the actual error message text associated with the error.
ERROR_SEVERITY(): Returns the severity level of the error (same as the second parameter in RAISERROR).
ERROR_STATE(): Returns the state number of the error.
ERROR_PROCEDURE(): Returns the name of the stored procedure or trigger where the error originated.

THROW Statement

It is used to raise an exception and terminate the batch, function, or procedure. It simplifies error handling by eliminating the need for RAISEERROR and providing clearer control over error messages.

BEGIN TRY
    -- T-SQL statements
    THROW 50001, 'Custom error message', 1;
END TRY
BEGIN CATCH
    -- Error handling code
END CATCH

Nested TRY-CATCH Blocks

We can place a try block inside another try block. This allows us to handle exceptions at different levels of our code.

BEGIN TRY
    BEGIN TRY
        -- Nested T-SQL statements
    END TRY
    BEGIN CATCH
        -- Nested error handling code
    END CATCH
END TRY
BEGIN CATCH
    -- Outer error handling code
END CATCH

Example

BEGIN TRY  -- Outer TRY block
  PRINT 'Outer TRY block executing...'

  BEGIN TRY  -- Inner TRY block
    -- Code that might generate an error (e.g., division by zero)
    SELECT 1 / 0;
    PRINT 'Inner TRY block completed!'
  END TRY
  BEGIN CATCH  -- Inner CATCH block (for errors within inner TRY)
    PRINT 'Error in inner TRY block!';
    -- Handle the specific error here (e.g., log the error)
  END CATCH

  PRINT 'Continuing outer TRY block...'
END TRY
BEGIN CATCH  -- Outer CATCH block (for any errors not caught inside)
  PRINT 'Error in outer TRY block or inner TRY block not handled!';
  -- Handle unhandled errors here (e.g., notify administrator)
END CATCH

In this example, an error in the inner TRY block will be caught by the inner CATCH block, and execution will continue in the outer TRY block. If the error isn't handled there, the outer CATCH block will take over.

Logging Errors

It's a good practice to log errors when they occur, especially in production environments. You can insert error details into a logging table or write them into a file.

BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;
    SELECT @ErrorMessage = ERROR_MESSAGE(),
           @ErrorSeverity = ERROR_SEVERITY(),
           @ErrorState = ERROR_STATE();
    -- Log error to a table
    INSERT INTO ErrorLog (ErrorDateTime, ErrorMessage, ErrorSeverity, ErrorState)
    VALUES (GETDATE(), @ErrorMessage, @ErrorSeverity, @ErrorState);
END CATCH

We can fetch error details from the ErrorLog table.

Handling Transactions

Always include a ROLLBACK TRANSACTION statement in the CATCH block to roll back any uncommitted transactions in case of an error.

BEGIN TRY
    BEGIN TRANSACTION;
    -- T-SQL statements
    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
    -- Log error and handle accordingly
    INSERT INTO ErrorLog (ErrorDateTime, ErrorMessage)
    VALUES (GETDATE(), ERROR_MESSAGE());
END CATCH

Handling Specific Errors

Use the ERROR_NUMBER() function within the CATCH block to selectively handle specific error numbers or ranges of error numbers. This allows you to customize error handling based on the type of error encountered.

BEGIN TRY
    -- T-SQL statements
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 1205
    BEGIN
        -- Handle deadlock error
    END
    ELSE IF ERROR_NUMBER() = 2627
    BEGIN
        -- Handle unique constraint violation error
    END
    ELSE
    BEGIN
        -- Handle other errors
    END
END CATCH

Handling Timeouts

Set a timeout and handle the error using ERROR_NUMBER().

BEGIN TRY
    SET TIMEOUT 10; -- Example: set a timeout
    -- T-SQL statements
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = -2
    BEGIN
        -- Handle timeout error
    END
    ELSE
    BEGIN
        -- Handle other errors
    END
END CATCH

Custom Error Messages

Use RAISEERROR or THROW to provide informative error messages that help in debugging and troubleshooting.

BEGIN TRY
    -- T-SQL statements
END TRY
BEGIN CATCH
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();
    -- Log error with custom message
    RAISEERROR('An error occurred: %s', 16, 1, @ErrorMessage);
END CATCH

Implementing these techniques ensures that your T-SQL code is robust and capable of handling various error scenarios gracefully, providing better reliability and maintainability for your database applications.