stored proc - default return value
-
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'
-
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
-
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.
-
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