locked
Return Value in stored Procedure RRS feed

  • Question

  • Is it safe to use both return value and output parameter in same stored procedure ?
    Monday, June 18, 2012 1:11 PM

Answers

  • Yes, but keep in mind that the intended purpose of the return value is to indicate success or failure (zero for success, non-zero for error) rather than return data to the calling application.  Use output parameters or resultsets to return data to the caller.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Naomi N Monday, June 18, 2012 1:36 PM
    • Marked as answer by amber zhang Monday, June 25, 2012 2:18 AM
    Monday, June 18, 2012 1:16 PM

All replies

  • There is no reason that you cannot use both


    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, June 18, 2012 1:12 PM
  • Yes, but keep in mind that the intended purpose of the return value is to indicate success or failure (zero for success, non-zero for error) rather than return data to the calling application.  Use output parameters or resultsets to return data to the caller.


    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/

    • Proposed as answer by Naomi N Monday, June 18, 2012 1:36 PM
    • Marked as answer by amber zhang Monday, June 25, 2012 2:18 AM
    Monday, June 18, 2012 1:16 PM
  • They are used with a distinct purpose, so there is no reason to not use them both.

    - Return value is used mainly to communicate sucess or failure. Notice that the data type is integer and can't be changed.

    - Output parameter is used to exchange data between stored procedures and the application that called them (could be another sp). The data type of the output parameter not necessarily need to be integer.


    AMB

    Some guidelines for posting questions...


    • Edited by HunchbackMVP Monday, June 18, 2012 1:24 PM
    • Proposed as answer by Naomi N Monday, June 18, 2012 1:36 PM
    Monday, June 18, 2012 1:23 PM
  • Others have already given the correct answer. But you should generally prefer a return value over an out param. Out params are a neccissary evil if you find yourself writing code that needs to do 2 things.


    Manas Bhardwaj's Stream : www.manasbhardwaj.net

    Monday, June 18, 2012 1:33 PM
  • Others have already given the correct answer. But you should generally prefer a return value over an out param. Out params are a neccissary evil if you find yourself writing code that needs to do 2 things.


    Manas Bhardwaj's Stream : www.manasbhardwaj.net

    That makes no sense.  return values and output parameters perform 2 completely different functions.


    Chuck Pedretti | Magenic – North Region | magenic.com

    Monday, June 18, 2012 1:34 PM