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