PrismitaLEARN · PRACTICE · GROW

How do you handle errors in T-SQL (TRY/CATCH, THROW, RAISERROR)?

SQL Server T-SQL Intermediate 2–3 Yrs3–5 Yrs SQL Query 1 min read

Quick Answer

Wrap the work in BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH. In the CATCH block, roll back if a transaction is open (IF @@TRANCOUNT > 0 ROLLBACK) and re-raise with THROW (which preserves the original error). Set SET XACT_ABORT ON so that severe errors abort the whole transaction automatically.

I use BEGIN TRY / BEGIN CATCH. In the catch block I check @@TRANCOUNT and roll back if there's an open transaction, and then I use THROW to re-raise the error — THROW keeps the original error number and message, unlike the old RAISERROR. I also put SET XACT_ABORT ON at the top of the procedure so that a serious error rolls the transaction back instead of leaving it partially applied. One caveat: TRY/CATCH won't catch compile-time errors or connection-killing severity levels.

  • THROW (SQL 2012+) — re-raises the caught error unchanged, or raises a new one; simpler and more correct than RAISERROR.
  • RAISERROR — older, lets you format messages and set severity/state; still used for custom messages and for progress output with WITH NOWAIT.
  • SET XACT_ABORT ON at the top of every proc — ensures a runtime error rolls the transaction back rather than leaving it half-done.
  • TRY/CATCH does not catch compile errors, severity 20+ connection-terminating errors, or KILL.
  • ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_LINE() are available inside CATCH.

SQL — proc error handling 
CREATE PROC dbo.usp_Transfer @from int, @to int, @amt money AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;
    BEGIN TRY
        BEGIN TRAN;
        UPDATE Accounts SET Balance -= @amt WHERE Id = @from;
        UPDATE Accounts SET Balance += @amt WHERE Id = @to;
        COMMIT;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK;
        THROW;   -- re-raise, keep original error
    END CATCH
END
All SQL Server questions More on T-SQL