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.
- StartTRY block runsStatements inside a transaction
- Step 2A statement failsControl jumps to CATCH
- Step 3Roll backIf XACT_STATE() is not 0
- Step 4Log the detailsERROR_NUMBER, ERROR_MESSAGE, ERROR_PROCEDURE
- 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.
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.
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.
Want the full details? Read the official Microsoft documentation for this topic.