views:

252

answers:

4

Hello,

Could anyone point exactly where MSDN says thet every user stored procedure returns 0 by default if no error happens? In other words, could I be sure that example code given below when being a stored procedure

IF someStatement
BEGIN
  RETURN 1
END

should always return zero if someStatement is false and no error occurs?

I know that it actually works this way, but I failed to find any explicit statement about this from Microsoft.

A: 

if you are going to switch some logic on that in your client, why not return "0" and make it more explicit?

Tim Mahy
Why explicitly return "0" if it is guaranteed to be returned? That's all the point of my question - is it really guaranteed (by some standard which SQL Server is claimed to comply with, for example), or this behavior is just optional or, moreover, to some extent even occasional?
Ilya
+3  A: 

RETURN on MSDN

Edit:

The link says

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

One could say that no RETURN = RETURN NULL = RETURN 0. But no warning is issued because you have not run RETURN NULL. And zero is expected because it's a stored procedure.

Also, stored proc execution allows for

EXEC @rtn = uspMyProc @p1...

So something must be returned because we always expect a value, never NULL

I've relied on zero being returned for 12 years, even if MSDN doesn't say. As well as many more of us :-)

gbn
That's all the trick that I look through this article again and again and cannot find any concrete, precise statement about stored procedure default return value. The only one I found a bit relevant is below:"Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure"But this is about system stored procedures, not user defined. Or, maybe, one should consider user sp call being a call to system sp primarily? This version seems to me pretty improbable.
Ilya
As I could understand, the article you pointed above even doesn't state that RETURN with no arguments results in returning zero value.Besides, all examples listed there don't contain any case of my interest (first example doesn't explain what should be actually returned, it just stated that execution of sp will be terminated and the other one deals with non-zero return values only)
Ilya
A: 

Returning Data by Using a Return Code from Books Online.

John Saunders
This article states nothing about default return value. The examples there use explicitly returned zero to indicate success.
Ilya
+3  A: 

It looks like once upon a time the return value may have meant something (see reference of pre SQL 2000 BOL return value article) prior to SQL Server 2000. I checked around to see if I could find a listing of these original return codes specifically for MS SQL, and found the following (I don't know its authenticity though, and it doesn't actually list the values).

So, after reading all of these articles it looks like @return_status is a parameter that is ALWAYS returned when a stored procedure is executed (even if you do not use it). According to the RETURN Books online article the return code CANNOT be null.

When used with a stored procedure, RETURN cannot return a null value. If a procedure tries to return a null value (for example, using RETURN @status when @status is NULL), a warning message is generated and a value of 0 is returned.

Running the following T-SQL definitely shows this,

create Procedure Test
as
begin
DECLARE @RTN integer
Return @RTN
END
GO

Exec Test
GO

Drop Procedure Test
GO

You'll receive

The 'Test' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.

In the end it looks like the answer is because @return_status cannot be null (being 0 when not set, or set to NULL)...

As for the error codes mentioned in the supposed BOL article for SQL 7.0, my guess would be an old hold over from Sybase... Sybase 5.0 Manual

Kris

KSimons
Your SQL 7 link looks OK. There are copies of BOL dotted around the electric interweb that occasionally surface here, but in this case it contributes nicely.
gbn