SQL for .NET developers Intermediate

Error handling with TRY...CATCH

T-SQL has TRY...CATCH blocks, much like C#. This lesson shows how to catch errors, roll back cleanly, read the error details, raise your own errors with THROW, and how those errors reach your .NET code as SqlException.

What happens on an error
  1. StartTRY block runsStatements inside a transaction
  2. Step 2A statement failsControl jumps to CATCH
  3. Step 3Roll backIf XACT_STATE() is not 0
  4. Step 4Log the detailsERROR_NUMBER, ERROR_MESSAGE, ERROR_PROCEDURE
  5. EndTHROWCaller gets a SqlException with the number

TRY...CATCH

Statements in the TRY block run normally. If one fails, control jumps to CATCH, where functions such as ERROR_NUMBER() and ERROR_MESSAGE() describe the error.

SQL
SET XACT_ABORT ON;
BEGIN TRY
    BEGIN TRAN;
        UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountId = @From;
        UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountId = @To;
    COMMIT;
END TRY
BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK;

    INSERT dbo.ErrorLog (Number, Message, ProcName, LoggedAt)
    VALUES (ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_PROCEDURE(), SYSUTCDATETIME());

    THROW;   -- re-raise the original error to the caller
END CATCH;

The important details

  • Always roll back in CATCH when XACT_STATE() is not 0, or the transaction stays open and holds locks.
  • Re-raise with a bare THROW so the caller knows it failed; swallowing errors hides bugs.
  • THROW 50001, 'Message', 1 raises your own error; numbers 50000 and up are for you. Prefer THROW over the older RAISERROR.
  • Some errors cannot be caught in the same scope, such as compile errors on a missing table. They are caught by the caller.

Errors in .NET

A SQL error arrives in C# as SqlException (inside DbUpdateException for EF Core SaveChanges). Its Number tells you what went wrong, so you can show a friendly message for expected cases.

C#
try
{
    await db.SaveChangesAsync();
}
catch (DbUpdateException ex) when (ex.InnerException is SqlException sql)
{
    var message = sql.Number switch
    {
        2627 or 2601 => "That email is already registered.",   // unique key
        547          => "This item is still in use.",          // FK or CHECK
        50001        => sql.Message,                             // our own THROW
        _            => null
    };
    if (message is null) throw;
    ModelState.AddModelError("", message);
}

Interview question

What does XACT_STATE() tell you in a CATCH block?

It returns 1 if a transaction is open and can still be committed, -1 if a transaction is open but doomed (it can only be rolled back), and 0 if there is no transaction. In CATCH you normally roll back when it is not 0. With SET XACT_ABORT ON most errors make it -1.

Your progress is saved only in this browser.

Want the full details? Read the official Microsoft documentation for this topic.