error codes 2

[crystalized]

Hello All,

I have been writing a number of stored procedures with a fair amount of business logic built in. Now I often am using return values to indicate some specific thing related to that business logic. Primarily I have been using return values such as -1, -2 etc. I discovered the list of error messages and codes in the master.sysmessages table and they are all positive numbers so I thought using the negative numbers was an okay plan.

BUT..... I am almost certain that when I have used query analyzer for certain tasks I have recieved errors with negative number codes (specifically I am almost certain I got a -4 which had to do with relationship constraints or duplicate primary keys).

So my question is this: Is there another list of error codes available that I should watch out to not use, or are those error codes I mentioned just specific to query analyzer?

Any info on this would be appreciated.

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[bperry]

Hi there,
My understanding is that Microsoft uses negative integers to indicate error conditions. Therefore, it is very common to return 0 and positive numbers from your user-written SPs: 0 meaning success, and positive integers indicating a failure (and a reason for failure: 1 means this problem, 2 means that problem, etc


>>I often am using return values to indicate some
>>some specific thing related to that business logic
This is also very common: you will see folks returning all manner of things via RETURN: last identity value, number of rows inserted, stuff like this. However, I think this technique is being discouraged, and and is not considered good practice by many of the authors that I have looked at. RETURN should be reserved for success/failure & error conditions, but data items from business logic should be returned to the client via SP OUTPUT parameters, not RETURN.

"The idea is that using RETURN values as a means of returning data back to the calling routine clouds the meaning of the return code when you need to send back honest-to-goodness error codes." [Vierra, pg 387]

Hope this helped a bit. (It was probably too long.)

bperry

 

[crystalized]

bperry

Thanks for the info. I am actually using the returns more as error codes like you mentioned in your first paragraph - this number means this problem etc but at least I know what the error was.

My biggest concern is not using the same codes as built in errors. So for example if I check for an error condition and want to return the error code, I do not want a built in error code to be returned and think it was my error that occurred, because I may handle my error differently than an error that the system generated.

Also you mention that you think they use negative numbers to indicate errors. Yet when I looked in BOL I found a section indicating that sysmessages is where the error codes and descriptions are stored. But when I looked at that table's contents all the error codes were positive numbers. So I still wonder where the negative numbers come from and if there is a place where I can see what they are. I can of course change to positive numbers for my error codes and compare to sysmessages but I am still wondering about other error codes that are not in sysmessages.
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[cmmrfrds]

If my memory serves me right, Microsoft and Sybase before them have recommended user errors of 50000 or above in SQL Server. From BOL.

Adding User-Defined Error Messages
User-defined error messages can be added to the sysmessages table using the system stored procedure sp_addmessage. At a minimum, you can specify the message number, the severity level, and the message text using sp_addmessage.

Similar to specifying user-defined error messages with RAISERROR, use error message numbers greater than 50000 and severity levels from 0 through 18. Only system administrators can issue RAISERROR with a severity level from 19 through 25.

 

[bperry]

I'm not sure I have a definitive answer for you, but I don't think this is seen as a big problem, and you will not be running into error number conflicts. i.e. if your stored procedure bombs off, Microsoft doesn't insert its error number into the SP return value: any values in there will be yours.

I believe fatal SP errors will invoke a RAISERROR, but that is handled in a different way, for example it will be seen as part of the errors collection on a connection object in ADO.

Perhaps other forum readers can add some more here. But mostly don't worry: you're unlikely to have any problems or conflicts here.

bp

 

[crystalized]

Thanks to both of you for your quick replies.

I had glanced at the addmessage bit but obviously did not read the whole thing or I would have noticed the 50000 info. (I don't actually have reusable errors so I probably won't add them to sysmessages - but it is an interesting thought)

Well I guess I should be okay (but I still think I might go and change the numbers I am using) Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde