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