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?

Answer 1

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.............

Answer 2

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

Answer 3

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.

Answer 4

RETURN is used to return integers.

 

---

Abdallah El-Chal, PMP, ITIL, MCTS

Yes, that's another difference. Thank you.

Answer 5

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.
*/

 

Answer 6

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.

Answer 7

Yes, all things considered, I see that output params are preferred.

Answer 8

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, [e-mail]
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

Answer 9

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!

Answer 10

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"...