none
Stored Procedure, when to use Output paramemter vs Return variable

    Question

  • When would you use an output parameter vs a return variable, or vice versa? In the following simple example, I can achieve the samething using either one.

    Using output parameter

    create proc dbo.TestOutput (@InValue int, @OutValue int output)
    as
    set @OutValue = @InValue
    
    declare @x int
    exec TestOutput @InValue = 3, @OutValue = @x output
    select @x

     

    Using return variable:

    create proc dbo.TestReturn (@InValue int)
    as
    return @InValue
    
    declare @x int
    exec @x = TestReturn @InValue = 3
    select @x

     

    As you can see, they both do the samething. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?

    Tuesday, September 28, 2010 5:34 PM

Answers

  • Try this. RETURN does the job of OUTPUT in some cases. RETURN is used to return integers. You can have multiple OUTPUT, but one RETURN.

    create proc dbo.TestOutput (@InValue varchar(20), @OutValue varchar(20) output)
    as
    set @OutValue = @InValue
    
    declare @x varchar(20)
    exec TestOutput @InValue = 'abc', @OutValue = @x output
    select @x
     
    
    Using return variable:
    
    create proc dbo.TestReturn (@InValue varchar(20))
    as
    return @InValue
    
    declare @x varchar(20)
    exec @x = TestReturn @InValue = 'abc'
    select @x
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by DoolinDalton Tuesday, September 28, 2010 5:47 PM
    Tuesday, September 28, 2010 5:41 PM
  • By using Output parameters, u can get more than one value... In otherwords, u can have more than one output parameter...

     

    But By using Return,we can return only one value i think.............

    • Marked as answer by DoolinDalton Tuesday, September 28, 2010 5:47 PM
    Tuesday, September 28, 2010 5:40 PM

All replies

  • By using Output parameters, u can get more than one value... In otherwords, u can have more than one output parameter...

     

    But By using Return,we can return only one value i think.............

    • Marked as answer by DoolinDalton Tuesday, September 28, 2010 5:47 PM
    Tuesday, September 28, 2010 5:40 PM
  • Try this. RETURN does the job of OUTPUT in some cases. RETURN is used to return integers. You can have multiple OUTPUT, but one RETURN.

    create proc dbo.TestOutput (@InValue varchar(20), @OutValue varchar(20) output)
    as
    set @OutValue = @InValue
    
    declare @x varchar(20)
    exec TestOutput @InValue = 'abc', @OutValue = @x output
    select @x
     
    
    Using return variable:
    
    create proc dbo.TestReturn (@InValue varchar(20))
    as
    return @InValue
    
    declare @x varchar(20)
    exec @x = TestReturn @InValue = 'abc'
    select @x
    


    Abdallah El-Chal, PMP, ITIL, MCTS
    • Marked as answer by DoolinDalton Tuesday, September 28, 2010 5:47 PM
    Tuesday, September 28, 2010 5:41 PM
  • By using Output parameters, u can get more than one value... In otherwords, u can have more than one output parameter...

     

    But By using Return,we can return only one value i think.............


    Ahh, that's definitely a difference.
    Tuesday, September 28, 2010 5:43 PM
  • RETURN is used to return integers.

     


    Abdallah El-Chal, PMP, ITIL, MCTS


    Yes, that's another difference. Thank you.

    Tuesday, September 28, 2010 5:47 PM
  • Another potential issue would be related to the number of digits that you might want to return.  For instance, if you want to return 20 digits it will be necessary to use an output parameter rather than the return value.  For example:

    create procedure dbo.something
    as
    return 12345678901234567890
    go
    
    exec dbo.something
    
    /* -------- Output: --------
    Msg 8115, Level 16, State 2, Procedure something, Line 3
    Arithmetic overflow error converting expression to data type int.
    The 'something' procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
    */
    
    

     

    Tuesday, September 28, 2010 5:47 PM
    Moderator
  • This is T-SQL, not C. Never use return values, many client side APIs make dealing with return values a pain if not plain impossible. Always use OUTPUT parameters.

    Many use return values only to inform of success or failure and output parameters for anything that needs to be returned.

    An alternative is to...

    Use a return value when you only need to return one item.

    Use output parameters when you need to return more than one value.

    ALSO,

    Since return values only work with int, it ends up being "inconsistent". I prefer the output param for consistency.

    Also, output params force the caller to recognize the returned value. IME, return values are routinely ignored.

    • Edited by SqlRockss Tuesday, September 28, 2010 5:49 PM adding
    • Proposed as answer by Naomi NModerator Tuesday, September 28, 2010 5:50 PM
    Tuesday, September 28, 2010 5:47 PM
  • Yes, all things considered, I see that output params are preferred.
    Tuesday, September 28, 2010 6:32 PM
  • In addition to the other posts, return values are convemtionally used for returns status only. 0 is success, and everyhing else is an error. You should not use return values to return data like an order id.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Links for SQL Server Books Online:
    SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
    SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
    SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
    Tuesday, September 28, 2010 9:01 PM
  • Hey, @SqlRockss: could you explain how return values end up being "inconsistent"?

    Or did the people whose answers you copied and pasted without attribution, from your sockpuppet reposting of this question on Stack Overflow , not mention that bit?

    @Asken12345:

    By all means learn from other people; what else are websites like this forum and Stack Overflow for? But if he was trying to be helpful, rather than just rep whoring as fast as possible (take a look at his question history), it would've been quicker, easier and more honest to reply with a link to his reposted questions on SO rather than copying the answers he got verbatim .

    If he had rewritten them, as you suggest, that would have been preferable: he'd've been forced to understand (even a little) what he was ripping off, and might actually have learned something and been able to speak with some credibility. Instead, it's clear he didn't even read the responses he got: in his answer to this question , other MSDN users end up addressing him as "fritz", because that's how a Stack Overflow user signed off in the answer he posted !

    Credit where credit is due!

    • Edited by shambulator Monday, November 08, 2010 11:04 AM Plagiariser's username changed
    Wednesday, September 29, 2010 8:16 AM
  • Who cares how and where he got the answer? He was being helpful and I'm sure DoolinDalton is probably quite happy if the answer was useful.

    How did I learned all I know about dev stuff? Of course by ripping code and ideas from other people as I'm sure most of us have. He could just have re-writted it and then we'd never be the wiser. The answer would however be just a valuable.

    If it bothers you just don't award him the "answer"...

    Monday, October 04, 2010 7:30 AM