T-SQL TRY...CATCH, THROW and RAISERROR

A transfer of 5,000.00 inside BEGIN TRANSACTION fails on its debit with error 547, the credit after it runs anyway, and the COMMIT at the end keeps that credit: 5,000.00 created from nothing. T-SQL reports many run-time errors without stopping the batch, so code that must stop has to catch the error and decide itself what to undo.

You will catch errors with T-SQL TRY…CATCH and read what the ERROR_ functions report, reproduce the partial commit that an uncaught error leaves behind, raise your own errors with THROW and RAISERROR, meet the errors that no CATCH block at the same level receives, and write a CATCH block that cleans up, logs and raises the original error again.

A TRY block hands its first error to the CATCH block

T-SQL TRY…CATCH is a pair of blocks: BEGIN TRY … END TRY, followed at once by BEGIN CATCH … END CATCH. When a statement in the TRY block raises an error, the rest of the block is skipped and control passes to the first statement of the CATCH block; when nothing fails, the CATCH block is skipped. Microsoft’s page states what is caught: every run-time error with a severityerror severity: The level from 0 to 25 that SQL Server gives every message: 10 or lower is information, 11 to 16 errors the user can correct, 17 to 19 software errors, and 20 or more fatal errors that stop the task. above 10 that does not close the database connection. An error inside a procedure called from the TRY block is caught too, unless the procedure has a TRY...CATCH of its own. A TRY...CATCH cannot span two batches and cannot be used inside a user-defined function. Step through a transfer that fails inside a procedure:

Inside the CATCH block, six functions describe the error, and the table after the script lists what each returns for the failed debit. Outside the CATCH block, each returns NULL, which the script’s last SELECT shows:

-- Engine: SQL Server 2025 CU9 (17.0.5005.3); database lab with the practice schema. Client: sqlcmd.
-- An error inside a procedure, caught by the caller's TRY...CATCH, with the six ERROR_ functions. dbo.post_pair moves
-- money between two accounts; account 3 holds 760.25, so taking 5,000.00 from it breaks accounts_balance_ck.
CREATE OR ALTER PROCEDURE dbo.post_pair
  @from   int,
  @to     int,
  @amount decimal(14,2)
AS
BEGIN
  SET NOCOUNT ON;
  UPDATE dbo.accounts SET balance = balance + @amount WHERE account_id = @to;     -- the credit runs first
  UPDATE dbo.accounts SET balance = balance - @amount WHERE account_id = @from;   -- error 547 when @from is short
END;
GO
SET NOCOUNT ON;
BEGIN TRY
  BEGIN TRANSACTION;
  EXEC dbo.post_pair @from = 3, @to = 1, @amount = 5000.00;
  COMMIT TRANSACTION;                                     -- skipped: control left the TRY block at the error
  PRINT N'not printed';
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;                -- undo the credit as well
  SELECT ERROR_NUMBER()    AS number,                     -- 547
         ERROR_SEVERITY()  AS severity,                   -- 16
         ERROR_STATE()     AS state,
         ERROR_PROCEDURE() AS in_procedure,               -- dbo.post_pair (with the schema since SQL Server 2017)
         ERROR_LINE()      AS at_line,                    -- the line of the second UPDATE inside the procedure
         ERROR_MESSAGE()   AS message;                    -- ... conflicted with the CHECK constraint "accounts_balance_ck" ...
END CATCH;
SELECT ERROR_NUMBER() AS outside_a_catch_block;           -- NULL
SELECT account_id, balance FROM dbo.accounts WHERE account_id IN (1, 3) ORDER BY account_id;   -- 2500.00, 760.25
DROP PROCEDURE dbo.post_pair;
GO

"""try_catch_basics.sql in the lab: sqlcmd runs it as sa in the database lab, on a freshly loaded practice schema."""

from mssql_session import Lab

with Lab() as lab:
    lab.sqlcmd("basics/try_catch_basics.sql")

$ sqlcmd -S localhost -U sa -C -d lab -i basics/try_catch_basics.sql
number severity state in_procedure  at_line message
------ -------- ----- ------------- ------- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
   547       16     0 dbo.post_pair      12 The UPDATE statement conflicted with the CHECK constraint "accounts_balance_ck". The conflict occurred in database "lab", table "dbo.accounts", column 'balance'.
outside_a_catch_block
---------------------
                 NULL
account_id balance
---------- -------
         1 2500.00
         3  760.25

One more rule changes how callers see errors: Microsoft notes that an error trapped by a CATCH block is not returned to the calling application. Unless the CATCH block sends it on, the client sees a batch that succeeded.

Sending an error on, and raising errors of your own, is the work of two statements. THROW raises an error with your own number and message, and Microsoft recommends it for new code. RAISERROR is the older statement, which formats its message and still runs in much existing code.

Partial commits, and raising errors with THROW and RAISERROR Pro: opens with a Pro, Premium or Ultimate pass

Uncaught errors, and a CATCH block in the right order Pro: opens with a Pro, Premium or Ultimate pass

Practise: follow the error to where it lands

Each question shows a statement that fails, or a CATCH block. Decide where control goes, what is kept and what the caller sees, then read why each answer is right or wrong:

Interview questions

Warm-up (fresher to 2–5 years): what happens when a statement inside a TRY block fails, and what does the caller see?

The rest of the TRY block is skipped and control passes to the first statement of the CATCH block; when nothing fails, the CATCH block is skipped. The CATCH block receives every run-time error with a severity above 10 that does not close the connection, including one raised inside a procedure that the TRY block called, unless that procedure has a TRY...CATCH of its own. Inside the CATCH block, ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), ERROR_PROCEDURE(), ERROR_LINE() and ERROR_MESSAGE() describe the error, and outside it they return NULL. A caught error is not returned to the caller: unless the CATCH block sends it on, the client sees a batch that succeeded.

Two longer questions follow, one about a transfer that committed half of its work and one about an error log that stays empty; answer each before you open its first step:

Key takeaways

• A TRY block passes its first run-time error of severity 11 or more to the CATCH block, where the ERROR_ functions describe it, and a caught error is not returned to the client.