MS SQL - Error Handling

How does TRY...CATCH work in T-SQL?

TRY...CATCH handles runtime errors in SQL Server.

• Put the statements that may fail inside the TRY block.

• If an error occurs, SQL Server stops the remaining statements in TRY.

• Control moves to the CATCH block.

• The CATCH block can log the error and roll back the transaction.

Basic syntax

BEGIN TRY
    -- Statements that may cause an error
END TRY
BEGIN CATCH
    -- Error-handling statements
END CATCH;

Simple example

BEGIN TRY
    SELECT 10 / 0;
END TRY
BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber,
        ERROR_MESSAGE() AS ErrorMessage;
END CATCH;

Result:

ErrorNumber   ErrorMessage
-----------   ------------------------
8134          Divide by zero error encountered.

Transaction example

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - 1000
    WHERE AccountId = 1;

    UPDATE Accounts
    SET Balance = Balance + 1000
    WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

If an error occurs:

1. The remaining TRY statements are skipped.

2. The transaction is rolled back.

3. THROW sends the original error back to the application.

Error functions

Inside CATCH, these functions provide error details:

Key points

• TRY...CATCH handles most runtime errors.

• Compile-time errors in the same scope may not be caught.

• Use it with transactions to prevent partial updates.

• Check the transaction using XACT_STATE() before rollback.

• Use THROW; to return the original error to the caller.

• TRY must be immediately followed by CATCH.

Difference between THROW and RAISERROR

Both are used to generate errors in SQL Server, but THROW is the modern and recommended option.

THROW example

IF NOT EXISTS
(
    SELECT 1
    FROM Accounts
    WHERE AccountId = 10
)
BEGIN
    THROW 50001, 'Account not found.', 1;
END;

THROW requires:

THROW error_number, message, state;

The custom error number must be 50000 or greater.

Rethrowing the original error

BEGIN TRY
    SELECT 10 / 0;
END TRY
BEGIN CATCH
    PRINT 'An error occurred';

    THROW;
END CATCH;

THROW; preserves the original:

• Error number

• Message

• Severity

• State

• Error line

RAISERROR example

DECLARE @AccountId INT = 10;

RAISERROR(
    'Account %d was not found.',
    16,
    1,
    @AccountId
);

Here:

• 16 is the severity.

• 1 is the state.

• %d is replaced with @AccountId.

Key points

• Prefer THROW for new T-SQL code.

• Use THROW; inside CATCH to rethrow the original error.

• Use RAISERROR mainly when legacy code needs custom severity or formatted messages.

• RAISERROR remains supported but is not recommended for new development.

How do you roll back a transaction when an exception occurs?

Use TRY...CATCH with BEGIN TRANSACTION, COMMIT, and ROLLBACK.

• Start the transaction inside TRY.

• Commit it when all statements succeed.

• If an error occurs, control moves to CATCH.

• Roll back the transaction inside CATCH.

Example

BEGIN TRY
    BEGIN TRANSACTION;

    UPDATE Accounts
    SET Balance = Balance - 1000
    WHERE AccountId = 1;

    UPDATE Accounts
    SET Balance = Balance + 1000
    WHERE AccountId = 2;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

How it works

If both updates succeed:

BEGIN TRANSACTION → UPDATE → UPDATE → COMMIT

If any update fails:

BEGIN TRANSACTION → ERROR → CATCH → ROLLBACK

ROLLBACK cancels all changes made after BEGIN TRANSACTION.

Why check XACT_STATE()?

IF XACT_STATE() <> 0
    ROLLBACK TRANSACTION;

XACT_STATE() returns:

Recommended version

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- Database operations

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

SET XACT_ABORT ON makes most runtime errors automatically mark or terminate the transaction. The CATCH block still performs the necessary rollback and returns the original error.

Key points

• Use explicit transactions for related operations.

• Put COMMIT at the end of the TRY block.

• Roll back only when a transaction exists.

• Use XACT_STATE() to check the transaction condition.

• Use THROW; to return the original error.

• Keep transactions short to reduce blocking and deadlocks.

What is XACT_STATE()?

XACT_STATE() is a SQL Server function that shows the current state of a transaction.

It is commonly used inside a CATCH block to decide whether the transaction should be committed or rolled back.

Return values

Example

SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRANSACTION;

    -- Causes an error
    INSERT INTO Orders(OrderId, Amount)
    VALUES (1, NULL);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    SELECT XACT_STATE() AS TransactionState;

    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    THROW;
END CATCH;

If the error makes the transaction uncommittable, XACT_STATE() returns -1. Therefore, the transaction must be rolled back.

Detailed handling

BEGIN CATCH
    IF XACT_STATE() = -1
    BEGIN
        -- Transaction is invalid
        ROLLBACK TRANSACTION;
    END
    ELSE IF XACT_STATE() = 1
    BEGIN
        -- Transaction is still valid
        -- Usually roll back when handling an error
        ROLLBACK TRANSACTION;
    END;

    THROW;
END CATCH;

XACT_STATE() vs @@TRANCOUNT

Key points

• XACT_STATE() = 1: transaction is active and committable.

• XACT_STATE() = -1: transaction is active but must be rolled back.

• XACT_STATE() = 0: no transaction exists.

• Use it in CATCH before executing ROLLBACK.

• XACT_STATE() does not indicate the number of nested transactions.

How do you log SQL errors without losing the original error details?

Inside the CATCH block:

1. Capture the original error using ERROR_*() functions.

2. Roll back the failed transaction.

3. Insert the captured details into an error-log table.

4. Use THROW; without parameters to return the original error.

Error-log table

CREATE TABLE ErrorLog
(
    ErrorLogId     INT IDENTITY PRIMARY KEY,
    ErrorNumber    INT,
    ErrorMessage   NVARCHAR(4000),
    ErrorSeverity  INT,
    ErrorState     INT,
    ErrorProcedure NVARCHAR(128),
    ErrorLine      INT,
    LoggedAt       DATETIME2 DEFAULT SYSDATETIME()
);

Example

BEGIN TRY
    BEGIN TRANSACTION;

    -- Database operations
    SELECT 10 / 0;

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    -- Capture original details before leaving CATCH
    DECLARE @ErrorNumber    INT            = ERROR_NUMBER();
    DECLARE @ErrorMessage   NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity  INT            = ERROR_SEVERITY();
    DECLARE @ErrorState     INT            = ERROR_STATE();
    DECLARE @ErrorProcedure NVARCHAR(128)  = ERROR_PROCEDURE();
    DECLARE @ErrorLine      INT            = ERROR_LINE();

    -- Roll back the failed transaction first
    IF XACT_STATE() <> 0
        ROLLBACK TRANSACTION;

    -- Log outside the rolled-back transaction
    INSERT INTO ErrorLog
    (
        ErrorNumber,
        ErrorMessage,
        ErrorSeverity,
        ErrorState,
        ErrorProcedure,
        ErrorLine
    )
    VALUES
    (
        @ErrorNumber,
        @ErrorMessage,
        @ErrorSeverity,
        @ErrorState,
        @ErrorProcedure,
        @ErrorLine
    );

    -- Rethrow the original error
    THROW;
END CATCH;

Why roll back before logging?

If the log record is inserted inside the failed transaction:

• It may also be rolled back.

• An uncommittable transaction (XACT_STATE() = -1) does not allow normal write operations.

Therefore, capture the error, roll back, and then log it.

Why use THROW;?

THROW;

Using THROW; without parameters preserves the original:

• Error number

• Message

• Severity and state

• Procedure and line number

Creating a new error with THROW 50000, ... or RAISERROR can replace some original details.

Key points

• Capture ERROR_*() values inside the CATCH block.

• Roll back before inserting into the error-log table.

• Use THROW; to preserve and return the original error.

• Include useful context such as procedure name, parameters, user, and correlation ID.

• Never log passwords, tokens, or sensitive information.

ASP.NET Core Web API calling T-SQL TRY...CATCH

When SQL Server executes THROW;, ASP.NET receives a SqlException. The API can catch it and return an appropriate response.

1. Install SQL Server package

dotnet add package Microsoft.Data.SqlClient

2. appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=.;Database=TestDb;Trusted_Connection=True;TrustServerCertificate=True"
  }
}

3. Program.cs

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers();

var app = builder.Build();

app.MapControllers();

app.Run();

4. Create controller

Controllers/SqlErrorController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.Data.SqlClient;
using System.Data;

namespace SqlErrorApi.Controllers;

[ApiController]
[Route("api/[controller]")]
public class SqlErrorController : ControllerBase
{
    private readonly IConfiguration _configuration;
    private readonly ILogger<SqlErrorController> _logger;

    public SqlErrorController(
        IConfiguration configuration,
        ILogger<SqlErrorController> logger)
    {
        _configuration = configuration;
        _logger = logger;
    }

    [HttpGet("divide-by-zero")]
    public async Task<IActionResult> DivideByZero()
    {
        const string sql = """
            BEGIN TRY
                SELECT 10 / 0;
            END TRY
            BEGIN CATCH
                PRINT 'An error occurred';
                THROW;
            END CATCH;
            """;

        try
        {
            string connectionString =
                _configuration.GetConnectionString("DefaultConnection")!;

            await using var connection =
                new SqlConnection(connectionString);

            await using var command =
                new SqlCommand(sql, connection);

            command.CommandType = CommandType.Text;

            await connection.OpenAsync();
            await command.ExecuteNonQueryAsync();

            return Ok(new
            {
                success = true,
                message = "SQL command executed successfully."
            });
        }
        catch (SqlException ex)
        {
            _logger.LogError(
                ex,
                "SQL error {ErrorNumber}: {ErrorMessage}",
                ex.Number,
                ex.Message);

            return StatusCode(
                StatusCodes.Status500InternalServerError,
                new
                {
                    success = false,
                    message = "A database error occurred.",
                    errorNumber = ex.Number,
                    errorMessage = ex.Message
                });
        }
    }
}

5. Call the API

GET /api/SqlError/divide-by-zero

Example:

https://localhost:7001/api/SqlError/divide-by-zero

Response

HTTP status:

500 Internal Server Error

JSON response:

{
  "success": false,
  "message": "A database error occurred.",
  "errorNumber": 8134,
  "errorMessage": "Divide by zero error encountered."
}

How the flow works

Web API
   ↓
Executes T-SQL
   ↓
SELECT 10 / 0 causes error
   ↓
SQL CATCH executes
   ↓
THROW returns the original SQL error
   ↓
ASP.NET catches SqlException
   ↓
API returns HTTP 500 response

Important points

• PRINT 'An error occurred' is only an informational SQL message. It does not become the API response.

• THROW; sends the original divide-by-zero error to ASP.NET.

• SQL Server error 8134 means divide by zero.

• Log detailed errors internally.

• In production, avoid returning ex.Message and other database details to clients. Return a safe message instead:

return StatusCode(500, new
{
    success = false,
    message = "An unexpected database error occurred."
});