Stored Procedure, when to use Output parameter 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 same thing 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 same thing. Can someone show me an example where the choice of a output parameter vs a return variable would make a difference?

Answer 1

I prefer:

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

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

Another common usage pattern is to use return values only to inform of success or failure and output parameters for anything that needs to be returned.

Answer 2

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.

Answer 3

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 4

You should use RETURN to return a value from a procedure much in the same way you'd use EXIT to return a value in a batch script. Return isn't really for parameter passing, but rather as a way to quit out of a procedure or query. As per MSDN documentation:

Unless documented otherwise, all system stored procedures return a value of 0. This indicates success and a nonzero value indicates failure.

This becomes more evident once you recognize the lack of any ability to define a type to your return value. It has to be INT.