What defines the default stored procedure return values?

[mreigler]

Really just a Friday afternoon curiosity.

A co-worker executed a stored procedure and received a return value of -7. They asked me what that meant. When needed I've checked return values to make sure they are zero or non-zero for success, but never really cared what the actual value is since I was more concerned with the actual SQL error. So I tried searching BOL, the internet and a couple books and nowhere can I find what the -7 means.

So then I created a stored procedure that would result in a divide by zero error. When I execute that I get a -6 as the return value.

So if anyone knows the quick answer where these values come from, I'll be able to rest easy over the weekend

Thanks Mike






Mike Reigler
Melange Computer Services, Inc

 

[JoeAtWork]

The return value is whatever you set it to in the stored procedure. I would expect that if you didn't explicitly set it to something that it would be 0. But you're the only one who can assign any real meaning to it by setting it explicitly in your stored procedure.

 

[mreigler]

Here is my test procedure:

Create proc usp_ReturnValueErrorTest

AS
DECLARE @x int
SET @x = 1/0
GO

So I'm not setting any return value, but it returns -6. On a test server we had procedure return -7 because the transaction log file was full even though the procedure didn't specifically return anything. Maybe there is no meaning to these values.






Mike Reigler
Melange Computer Services, Inc