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