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?

  • 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 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

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.


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.



  • 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
  • 2
    @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. 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
  • 4
    Yeah @AaronBertrand put up a bunch of your own points to compete against your correct answer.
    – Zane
    Mar 1 '13 at 14:47
  • 2
    @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

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).

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.