Raise Your Own Error in SQL Server: When Valid Code Breaks a Rule

Raise your own error in SQL Server when a valid statement breaks a rule you care about. SQL Server raises errors by itself when a statement can’t run. It stays silent when a statement runs fine and does the wrong thing. This post covers both.

The Sample Table

One table of library books. 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, Status VARCHAR(20) NOT NULL);
INSERT INTO Books (BookID, Title, Status) VALUES (1, 'Herb Gardens', 'On Shelf'), (2, 'Lisbon Walks', 'On Shelf'), (3, 'Soup Basics', 'On Shelf');
GO

Errors SQL Server Raises by Itself

SQL Server raises an error when a statement can’t, or shouldn’t, finish. The three statements below fail for three reasons. The first creates a table that already exists, which would overwrite the data. The second inserts NULL into a column that doesn’t allow it. The third drops a table that doesn’t exist.

CREATE TABLE Books (BookID INT PRIMARY KEY, Title VARCHAR(40) NOT NULL, Status VARCHAR(20) NOT NULL);
GO

INSERT INTO Books (BookID, Title, Status) VALUES (4, NULL, 'On Shelf');
GO

DROP TABLE ReadingList;

The messages are 2714, 515 and 3701. Each has a number, a severity level and a text. The query below reads them from sys.messages.

SELECT message_id, severity, text FROM sys.messages WHERE language_id = 1033 AND message_id IN (515, 2714, 3701) ORDER BY message_id;

The severity is 16 for 2714 and 515, and 11 for 3701. The texts hold placeholders such as %.*ls, which SQL Server fills with the name involved.

An Error That Depends on State

The same code can raise a different error, or none, depending on what exists. This procedure creates a table when it exists and drops it when it doesn’t. Either way, it fails.

CREATE PROCEDURE dbo.AlwaysFails
AS
BEGIN
  IF OBJECT_ID('dbo.ReadingList', 'U') IS NOT NULL
    CREATE TABLE dbo.ReadingList (BookID INT NOT NULL);
  ELSE
    DROP TABLE dbo.ReadingList;
END;
GO

With no ReadingList table, the procedure takes the ELSE branch and the DROP fails with message 3701.

EXEC dbo.AlwaysFails;

Now create the table by hand and call the procedure again. It takes the first branch, and the CREATE fails with message 2714. The last statement removes the table.

CREATE TABLE dbo.ReadingList (BookID INT NOT NULL);
GO

EXEC dbo.AlwaysFails;
GO

DROP TABLE dbo.ReadingList;

An error can come from the state of the database and not only from the code. Test a script against both states.

A Valid Statement That Breaks a Rule

Some problems aren’t SQL errors at all. This procedure sets the status of one book. A call for a book that doesn’t exist updates zero rows. That’s legal, so SQL Server says nothing.

CREATE PROCEDURE dbo.SetBookStatus @BookID INT, @Status VARCHAR(20)
AS
BEGIN
  UPDATE Books SET Status = @Status WHERE BookID = @BookID;
END;
GO

EXEC dbo.SetBookStatus 1, 'On Loan';
EXEC dbo.SetBookStatus 99, 'On Loan';

Both calls ran. The first changed one row, and the second changed none, with no error. If the ID was a typo, nobody finds out.

Add a check. @@ROWCOUNT holds the number of rows the last statement touched, so test it on the line right after it. RAISERROR raises an error of your own.

ALTER PROCEDURE dbo.SetBookStatus @BookID INT, @Status VARCHAR(20)
AS
BEGIN
  UPDATE Books SET Status = @Status WHERE BookID = @BookID;
  IF @@ROWCOUNT = 0
    RAISERROR('No book has that ID. Nothing was changed.', 16, 1);
END;
GO

EXEC dbo.SetBookStatus 2, 'On Loan';
EXEC dbo.SetBookStatus 99, 'On Loan';

The second call now fails with message 50000. SQL Server assigns 50000 to a RAISERROR that has a text and no message number. The 16 in the call is the severity.

THROW Does the Same Job

THROW has simpler syntax. You choose the error number, and it must be 50000 or higher.

ALTER PROCEDURE dbo.SetBookStatus @BookID INT, @Status VARCHAR(20)
AS
BEGIN
  UPDATE Books SET Status = @Status WHERE BookID = @BookID;
  IF @@ROWCOUNT = 0
    THROW 50001, 'No book has that ID. Nothing was changed.', 1;
END;
GO

EXEC dbo.SetBookStatus 99, 'On Loan';

The call fails with message 50001, at level 16.

Severity 10 Is Not an Error

RAISERROR at severity 10 or lower prints a plain message with no Msg line. Severity 11 and above produces an error. The first call below prints a note. The second raises an error.

RAISERROR('Just a note.', 10, 1);
GO

RAISERROR('Something is wrong.', 11, 1);

Clean Up

Remove the sample objects when you finish.

DROP PROCEDURE dbo.SetBookStatus;
DROP PROCEDURE dbo.AlwaysFails;
DROP TABLE Books;

What to Remember

SQL Server always raises an error when a statement can’t run. It raises one at severity 11 or higher when you ask it to. An update of zero rows is not on that list. Add the check when zero rows would be a mistake.

To go further, read THROW or RAISERROR, Reading SQL Server Error Messages Carefully and Pre-Flight Checks at the Top of a Script With THROW.

An error is not a crash, it is SQL Server telling you a rule was broken.

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