42

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?

Improve this question
2
  • 2
    You may reconsider your second example, as is it returns before doing any interesting work ;) Commented Sep 28, 2010 at 17:53
  • 1
    It does just as much work as the first example. Note the lack of BEGIN/END - each of those procs consist of a single statement. The second block in each example calls the first block. Commented May 31, 2012 at 1:15

7 Answers 7

Reset to default
21

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, although not my preference, is to use return values only to inform of success or failure and output parameters for anything that needs to be returned.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I liked this answer better before you edited it. Now it reads like "Many do this...but you could do this instead". You may wanna pick one :P
Just laying out the two main streams of thought on it. :-)
I believe a return value is meant to be a status code. It is limited to an int. So even using it to return the number of rows could blow up if the number of rows exceed the capacity of an int.
14

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.

Improve this answer

2 Comments

RETURN values indicating success/failure can be very useful when dealing with nested stored procedure calls.
I would say that BEGIN TRY/BEGIN CATCH and RAISERROR are way more useful than return values.
10

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.

Improve this answer

Comments

10

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.

Improve this answer

1 Comment

Exit (like a batch file errorlevel) is a good idea. I've also seen examples that put rowcount in there so you can see how many rows (if any) were affected.
3

taken from here

  • When you want to return one or more items with a data type then it is better to use an output parameter.
  • Generally, use an output parameter for anything that needs to be returned.
  • When you want to return only one item with only an integer data type then it is better to use a return value.
  • Generally, the return value is only to inform success or failure of the Stored Procedure.
  • A return List item a value of 0 indicates success and any non-zero value indicates failure.
Improve this answer

Comments

1

I will answer this question in different ways:

If you would like to return one value you have both the options. But if you would like to return multiple values you only need to stick with output parameters.

Second Scenario: In C# you have the control of type if you are using output parameters.

Third scenario: Function vs. Procedure select the one suiting to your needs.

Hope this helps

Improve this answer

Comments

1

I use return value to many things because it is more performative such as:

1 - When you insert or change an item when I do the validation in the database Return positive number to return the Identity and negative with the number of the error, it is faster.

2- When I make an appointment with paging use it to return the total amount of records is also faster and less costly. For the rest I use Output when there are several returns or different types of int. And of course I use recorset to return a list of items

How do I use the Dapper to make my queries I do not suffer.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.