debugsqlModerate

What is ERROR_STATE() in SQL Server and how it can be used?

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

canerror_statewhatserversqlusedhowand

Problem

I read that ERROR_STATE() can help to distinguish between different states/locations in the source code where same type of error can occur. But it is not really clear to me that how it can be useful.

MSDN states:

ERROR_STATE() Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

How it can be really used? Can some one give me an example, the ones provided in this reference article don't really help explain things well for me?

Solution

The purpose of SQL Server error states is for the SQL Server development team to be able to identify in code the exact place system errors were raised, given that many errors are raised in multiple places.

You as an end user (ie. developer of applications using SQL Server) can similarly use the state passed in to RAISERROR so that your product support can identify the place a procedure raises an error, for example:

create procedure usp_my_proc
as
if 
  raiserror(N'Error foo and bar', 16, 0);
if 
  raiserror(N'Error foo and bar', 16, 1);
go

See how the two state allow you to distinguish later which error case was hit. Before you say 'but I can look at the error message' I'm telling you one word: internationalization.

Code Snippets

create procedure usp_my_proc
as
if <somecondition>
  raiserror(N'Error foo and bar', 16, 0);
if <someothercondition>
  raiserror(N'Error foo and bar', 16, 1);
go

Context

StackExchange Database Administrators Q#35893, answer score: 12

Revisions (0)

No revisions yet.