Take the 2-minute tour ×

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:


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?

share|improve this question
Error_State and Error_Number combination will give you more clear picture about an error. Have a look at other error handling related functions here –  Ravindra Gullapalli Feb 27 '13 at 14:22
Thanks! But I had read this link earlier as well. It didn't help as not good examples are given for this on MSDN. That's why I raised question here. –  jaczjill Feb 27 '13 at 18:06
add comment

migrated from stackoverflow.com Mar 4 '13 at 13:06

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

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
if <somecondition>
  raiserror(N'Error foo and bar', 16, 0);
if <someothercondition>
  raiserror(N'Error foo and bar', 16, 1);

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.

share|improve this answer
Yes, this is what I was looking for..! :) all clear now! Thank you! –  jaczjill Mar 4 '13 at 18:09
very nice answer. the knowledge obtained from your post is of better understanding then this technet.microsoft.com/en-us/library/ms180031.aspx –  kashif Mar 10 at 18:01
add comment

No, it does not help you find anything out about where the error occurred. Here is a quick example. If you try to divide by 0, you get an error message with a bunch of details:



Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.

See the one called State, with a value of 1? ERROR_STATE() returns this value. So if you use TRY/CATCH:

  SELECT 1/0;



That's all. Not useful in most scenarios. I suggest more reading up on error handling in general before you dive too deep into specific functions that sound useful.



share|improve this answer
Thanks, it helped a bit. couldn't up vote your answer as I don't have required privilege. However, I wanted to get the reason of existence of this function with its example. MSDN says ERROR_STATE() returns the error state number, then what do I do with that!! –  jaczjill Feb 28 '13 at 8:48
@jaczjill sometimes you will want your application to respond a certain way depending on the error_state and the complexity of your error-handling architecture, but I suspect handling errors broadly like that is rare. Much more common with the error number itself (trapping specific errors) or the error severity. I don't recall ever seeing anyone use the error state in a meaningful way except in demos showing that it exists. –  Aaron Bertrand Feb 28 '13 at 11:33
alright, Thanks Aaron :) Also, could you set a bounty for this Question? so that it some techie take up as challange to get's its EXACT answer. Else you are the winner man. –  jaczjill Mar 1 '13 at 14:07
Yeah @AaronBertrand put up a bunch of your own points to compete against your correct answer. –  Zane Mar 1 '13 at 14:47
@jaczjill There are not so many techies who know more about SQL Server than Aaron, and most of them never show up here. And your question is answered here (most probably as exactly as it could go). –  dezso Mar 1 '13 at 14:51
show 3 more comments

Short answer - it can't. An ERROR_STATE is essentially a sub-division of an ERROR_NUMBER. It cannot tell you what line of code caused the error (except insofar as the ERROR_NUMBER and ERROR_STATE together tell you the cause of the error, and it then becomes obvious what the cause is).

share|improve this answer
Understood conceptually that it is to be used with ERROR_NUMBER() function always. However an example of its usage ERROR_STATE() with ERROR_NUMBER() will clarify the picture completely. OR any good reference link will be enough. –  jaczjill Feb 27 '13 at 18:03
add comment

Your Answer


