Post reply

Stored procedure return value

  • November 21, 2005 at 11:18 am

    #95740

    I've looked for the answer to this question everywhere and just can't find it.

    If a stored procedure is executed and an error occurs, if that stored procedure contains no explicit return statement, what is the range of values that it will return and what do they mean?

    For example, I've created a procedure such as the above, and when an insert command fails the stored procedure returns a value of -6. When the insert command in the proc is successful, the proc returns a value of zero. Is there any rhyme or reason to these values? Are they documented anywhere?

  • November 21, 2005 at 12:00 pm

    #605039

    Have you looked up error in BOL?  If you poke around may help...

    Error = 0 means EVERYTHING is ok.

     



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 21, 2005 at 12:06 pm

    #605041

    For return codes that are generated by SQL server, a zero means success and anything else means a failure. At one time, there was some meaning to the value of the return code but that is no longer true.

    Instead, the value of the error number and error text must be examined.

    Note that the value of error text is not available to stored procedures or batchs.

    SQL = Scarcely Qualifies as a Language

  • November 21, 2005 at 1:07 pm

    #605055

    In some cases the called procedure will fail and NOT set the return code at all.

    So you need to be careful.  If your @return_code was 0 before calling a stored procedure and that procedure fails without setting the return code it will still be 0 (never reset) and you might think all went well!!

    I think one example of this is when an insert fails because of a foreign key violation.

    Ron

  • November 29, 2005 at 2:27 pm

    #606549

    Thanks everyone!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply