June 23, 2009 at 7:49 pm
Where do the default return values come from?
eg.
If I have a stored procedure that doesnt have a return statement.
In that stored procedure I have a divide by zero error
The stored proc will have a return value of -6
Where does -6 come from?
ALTER PROCEDURE [dbo].[asdf_test]
AS
BEGIN
SET NOCOUNT ON
begin try
select 1/0 as 'an error'
end try
begin catch
select @@error as '0', ERROR_NUMBER() as '1', ERROR_SEVERITY() as '2'
select ERROR_STATE() as '3', LEFT(ERROR_PROCEDURE(),20) as '4', ERROR_LINE() as '5' ,
left(ERROR_MESSAGE(),40) as '6'
end catch
END
declare @i int
exec @i = [dbo].[asdf_test]
select @i as 'return value'
June 24, 2009 at 4:55 am
The simple answer is that SQL Server only guarantees to return zero on success - but then you knew that
If the stored procedure doesn't provide an explicit return value when an error is encountered, SQL Server will provide one for you. The only guarantee is that it will be non-zero, but in my experience it is always from the reserved range of values -99 to -1.
It does seem to be used internally by the server for some purpose, as the same error seems to produce the same return value - though this can, and does, vary between versions. It may also vary between Service Packs, Cumulative Updates, or the time of day
Being undocumented, one should clearly never rely on it for anything, but again I guess you are just asking out of curiosity, rather than having some crazy plan up your sleeve to utilize this behaviour somehow!
Paul
June 24, 2009 at 7:18 am
thanks, your right I dont have any plan to rely on -6 as my return value
it was actually just a question on stackoverflow that I thought I could answer with a bit of googling but didnt actually get anywhere...
http://stackoverflow.com/questions/1035789/return-value-from-a-stored-proc-on-error
do you have any links to documentation that cover your answer?
also feel free to add an answer on the above link
thanks again,
Paul.
June 24, 2009 at 7:28 am
Yeah I saw that
A quick Google turned up this: http://sqlserverpedia.com/wiki/Stored_Procedures_-_Output_Parameters_&_Return_Values
The guarantee on the zero return value for success is in Books Online.
Paul
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy