Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

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

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:

SELECT 1/0;

Result:

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:

BEGIN TRY
  SELECT 1/0;
END TRY
BEGIN CATCH
  SELECT ERROR_STATE();
END CATCH

Result:

----
   1

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.

http://msdn.microsoft.com/en-us/library/ms175976.aspx

http://www.sommarskog.se/error_handling_2005.html

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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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