T-SQL Errors and Reactions in SQL Server: SQL in Sixty Seconds #002

T-SQL errors and reactions are the subject of SQL in Sixty Seconds #002. Rick Morelan presents this episode from February 2012. It follows the first video in our short SQL Server series, and I added tests from SQL Server 2025.

We received a wonderful response to SQL in Sixty Seconds #001, which covered converting a subquery to a CTE. My co-authors, Vinod Kumar and Rick Morelan, and I regularly came across useful tips. We wanted to share those ideas in a short format.

Rick Morelan on Errors and Reactions

In this episode, Rick gives a visual explanation of SQL Server errors and reactions. The original episode remains below. This page preserves the 2012 introduction and Rick’s credit. A later companion episode provides the demo readers requested.

Continue With the Demo

The follow-up is SQL in Sixty Seconds #005: Errors and Reactions Demo. It continues the same topic with Rick Morelan. Keeping the two episodes together makes the original sequence easier to follow.

Three Reactions to an Error

An error ends one statement, the whole batch, or the transaction around it. Which one depends on the error and on a session setting. I ran each case below in a scratch database with one table of teas.

CREATE TABLE dbo.Teas (TeaId int NOT NULL PRIMARY KEY, TeaName varchar(20) NOT NULL);
INSERT dbo.Teas VALUES (1, 'Assam');

One Statement or the Whole Batch

Divide by zero stops only its own statement. The second query in this batch still ran and returned its row.

SELECT 1 / 0 AS Broken;
SELECT 'the next statement ran' AS Reaction;

SET XACT_ABORT ON changes that. The same two queries then stop at the error, and the second never runs. A conversion error behaves that way even with the setting off. Msg 245 ended the batch in my test.

SET XACT_ABORT ON;
SELECT 1 / 0 AS Broken;
SELECT 'the next statement ran' AS Reaction;
GO
SET XACT_ABORT OFF;
SELECT CAST('abc' AS int) AS Broken;
SELECT 'the next statement ran' AS Reaction;

What Happens to the Transaction

A duplicate key inside a transaction is the case that bites. With XACT_ABORT off, the failed insert stops, but the transaction stays open. @@TRANCOUNT was 1 and XACT_STATE() was 1. Your first insert is still waiting for a commit.

BEGIN TRANSACTION;
INSERT dbo.Teas VALUES (2, 'Rooibos');
INSERT dbo.Teas VALUES (1, 'Sencha');
SELECT @@TRANCOUNT AS OpenTransactions, XACT_STATE() AS XactState;
COMMIT;

Now repeat it with the setting on. The batch stopped at the duplicate, and the transaction rolled back. @@TRANCOUNT was 0, and the Jasmine insert before the error was gone.

SET XACT_ABORT ON;
BEGIN TRANSACTION;
INSERT dbo.Teas VALUES (3, 'Jasmine');
INSERT dbo.Teas VALUES (1, 'Sencha');
SELECT 'never reached' AS Reaction;
GO
SET XACT_ABORT OFF;
SELECT @@TRANCOUNT AS OpenTransactions, COUNT(*) AS Teas FROM dbo.Teas;

Missing Objects and Typos

A missing table is a runtime error. The statement before it ran, and the batch stopped at the error, so the statement after it never ran. A misspelled column fails at compile time instead. Nothing in the batch ran, not even the harmless SELECT before it.

SELECT 'before' AS Step;
SELECT * FROM dbo.NoSuchTable;
SELECT 'after' AS Step;
GO
SELECT 'before' AS Step;
SELECT TeaIdd FROM dbo.Teas;

TRY CATCH and What It Misses

TRY...CATCH handles most runtime errors, and the ERROR_ functions describe what happened. For the duplicate key it returned number 2627, severity 14, state 1 and the full message text.

BEGIN TRY
  INSERT dbo.Teas VALUES (1, 'Sencha');
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrNumber, ERROR_SEVERITY() AS Severity, ERROR_STATE() AS State,
         ERROR_LINE() AS Line, ERROR_PROCEDURE() AS ProcName, ERROR_MESSAGE() AS Message;
END CATCH;

It does not catch everything. A missing table in the same scope escaped my CATCH block, and SQL Server printed Msg 208. Put the same query inside a stored procedure and call it from the TRY block, and the error is caught. The procedure name came back in ERROR_PROCEDURE().

BEGIN TRY
  SELECT * FROM dbo.NoSuchTable;
END TRY
BEGIN CATCH
  SELECT 'caught' AS Result;
END CATCH;
GO
CREATE PROCEDURE dbo.ReadMissing AS SELECT * FROM dbo.NoSuchTable;
GO
BEGIN TRY
  EXEC dbo.ReadMissing;
END TRY
BEGIN CATCH
  SELECT ERROR_NUMBER() AS ErrNumber, ERROR_PROCEDURE() AS ProcName;
END CATCH;

RAISERROR and THROW

RAISERROR with severity 16 reports an error and lets the batch go on. THROW stops it. In the next script, the line after RAISERROR returned its row, and the line after THROW did not run. Inside a CATCH block, a bare THROW passes the original error on, with its number and message intact.

RAISERROR('raised with severity 16', 16, 1);
SELECT 'after RAISERROR' AS Step;
THROW 50001, 'thrown', 1;
SELECT 'after THROW' AS Step;

The Pattern I Use for a Write

I combine the three tools in every procedure that changes data. XACT_ABORT rolls back on error. TRY...CATCH gives me a place to react. THROW hands the original error to the caller. The check on XACT_STATE() avoids a rollback when nothing is open.

CREATE PROCEDURE dbo.AddTea @TeaId int, @TeaName varchar(20)
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;
  BEGIN TRY
    BEGIN TRANSACTION;
    INSERT dbo.Teas (TeaId, TeaName) VALUES (@TeaId, @TeaName);
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
  END CATCH;
END;
GO
EXEC dbo.AddTea 4, 'Matcha';
GO
EXEC dbo.AddTea 4, 'Matcha';
GO
SELECT @@TRANCOUNT AS OpenTransactions;

The second call failed with Msg 2627, the original duplicate key error, and @@TRANCOUNT was 0 afterwards. Errors with severity 20 or higher end the connection, so no code can react to them. The handling above covers severity 11 to 19.

More on Errors

• Explanation of TRY…CATCH and Error Handling
• Recycle the Error Log Without Restarting SQL Server
• SQL Joes 2 Pros: SQL Server Error Messages, Day 27 of 35

An error message is not the end of the explanation, it is the place to start asking questions.

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