TRY CATCH Block in SQL Server: Plan A, Plan B and the Rollback

A TRY CATCH block runs Plan A and falls back to Plan B when Plan A raises an error. Without one, the caller gets a raw error message. With one, you decide what happens next.

The Sample Tables

Three books and one loan. Loans points at Books with a foreign key, which matters in the last example. Run this in any test database. Every result in this post comes from SQL Server 2025.

CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(40) NOT NULL);
CREATE TABLE Loans (LoanID INT PRIMARY KEY, BookID INT NOT NULL REFERENCES Books (BookID), Borrower VARCHAR(20) NOT NULL);
INSERT INTO Books (BookID, Title) VALUES (1, 'Herb Gardens'), (2, 'Lisbon Walks'), (3, 'Soup Basics');
INSERT INTO Loans (LoanID, BookID, Borrower) VALUES (1, 1, 'Priya');
GO

Without a Block

The title column doesn’t allow NULL. This update tries it anyway, and the caller gets message 515 and a line saying the statement has been terminated.

UPDATE Books SET Title = NULL WHERE BookID = 1;

Plan A and Plan B

Put the code you hope will work in a BEGIN TRY block. That’s Plan A. Put the fallback in a BEGIN CATCH block. That’s Plan B. Only one of the two runs. When Plan A works, CATCH is skipped.

BEGIN TRY
  UPDATE Books SET Title = 'Herb Gardens, Second Edition' WHERE BookID = 1;
  PRINT 'Plan A worked.';
END TRY
BEGIN CATCH
  PRINT 'Plan B ran.';
END CATCH;

The update ran and printed Plan A worked. Plan B never ran.

Now make Plan A fail. The same kind of update sets the title to NULL. The error skips the rest of the TRY block, and CATCH runs instead. Inside CATCH, the ERROR_ functions describe what happened.

BEGIN TRY
  UPDATE Books SET Title = NULL WHERE BookID = 1;
  PRINT 'This line is skipped.';
END TRY
BEGIN CATCH
  PRINT 'Plan B. Error ' + CAST(ERROR_NUMBER() AS varchar(10)) + ', severity ' + CAST(ERROR_SEVERITY() AS varchar(10)) + ', line ' + CAST(ERROR_LINE() AS varchar(10)) + '.';
END CATCH;

The output shows error 515, severity 16, line 2. The PRINT after the update never ran. ERROR_MESSAGE() returns the full text of the error, if you want to log it. The other functions are ERROR_STATE and ERROR_PROCEDURE. All six work only inside a CATCH block. Outside one, they return NULL, as the next query shows.

SELECT ERROR_NUMBER() AS OutsideCatch;

What Counts as an Error

CATCH reacts to errors of severity 11 and higher. Severity 10 and lower is only a message. The first block below raises severity 10, and the TRY block carries on. The second raises severity 11, and CATCH takes over.

BEGIN TRY
  RAISERROR('Severity 10 is only a message.', 10, 1);
  PRINT 'Still in the TRY block.';
END TRY
BEGIN CATCH
  PRINT 'CATCH ran at severity 10.';
END CATCH;
GO

BEGIN TRY
  RAISERROR('Severity 11 is an error.', 11, 1);
  PRINT 'This line is skipped.';
END TRY
BEGIN CATCH
  PRINT 'CATCH ran at severity 11.';
END CATCH;

The first block printed its message and then the next line of the TRY block. The second block went straight to CATCH. Some errors can’t be caught at all, such as syntax errors in the same batch. The first link at the end explains them.

A Transaction Inside TRY

Here’s the case that surprises people. The script opens a transaction, deletes book 3, opens a second transaction and inserts a loan for book 3. The delete succeeds, because book 3 has no loans yet. The insert then fails the foreign key, because the book is gone.

BEGIN TRY
  BEGIN TRANSACTION;
  DELETE FROM Books WHERE BookID = 3;
  BEGIN TRANSACTION;
  INSERT INTO Loans (LoanID, BookID, Borrower) VALUES (2, 3, 'Marcus');
  COMMIT TRANSACTION;
  COMMIT TRANSACTION;
END TRY
BEGIN CATCH
  PRINT 'Error ' + CAST(ERROR_NUMBER() AS varchar(10)) + '. Open transactions: ' + CAST(@@TRANCOUNT AS varchar(5)) + '.';
  ROLLBACK TRANSACTION;
  PRINT 'Open transactions after the rollback: ' + CAST(@@TRANCOUNT AS varchar(5)) + '.';
END CATCH;
GO

SELECT
  (SELECT COUNT(*) FROM Books WHERE BookID = 3) AS Book3Rows,
  (SELECT COUNT(*) FROM Loans WHERE BookID = 3) AS LoansForBook3;

The error number is 547. CATCH sees two open transactions, because the inner COMMIT never ran. One ROLLBACK undoes everything and brings the count to 0. Book 3 is still there, and no loan exists.

Pass the Error On

Sometimes CATCH should log the problem and let the caller know. THROW with no arguments raises the original error again, with its original number. The caller then sees message 515, as if there were no CATCH.

BEGIN TRY
  UPDATE Books SET Title = NULL WHERE BookID = 2;
END TRY
BEGIN CATCH
  PRINT 'Logging the error, then passing it on.';
  THROW;
END CATCH;

Clean Up

Remove the sample tables when you finish.

DROP TABLE Loans;
DROP TABLE Books;

What to Remember

TRY is Plan A and CATCH is Plan B. An error skips the rest of TRY. CATCH runs for severity 11 and higher. Inside a transaction, one ROLLBACK undoes every open level, so check @@TRANCOUNT before you decide how to finish.

To go further, read Errors TRY CATCH Cannot Catch: Compile, Name and Severity Errors, Checking XACT_STATE Before COMMIT or ROLLBACK in a CATCH Block and Rethrowing Errors With THROW and Keeping the Original Number.

A TRY block is not a way to hide errors, it is a way to choose what happens after one.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.