SQL Stored Procedure with Output Parameter

Question

I know how to write a stored procedure with output parameters. But I don't know the reason why I would use it over just using a simple SELECT statement. A normal stored procedure can still return an output in a grid (see below examples).

Can anyone give a real life example where I can use an SP with output parameter over with SP without output parameter?

Examples

-- Using output parameter
SELECT @var = COUNT(*) FROM table1 WHERE gender = @gender...

-- Without output parameter
SELECT COUNT(*) FROM table WHERE gender = @gender...

Answer 1

Output parameters in stored procedures are useful for passing a value back to the calling T-SQL, which can then use that value for other things.

Let's say you have a stored procedure that returns a state given the input of a city, with state as the output parameter:

CREATE PROCEDURE [dbo].[GetStateFromCity] (@City NVARCHAR(30), @State NCHAR(2) OUTPUT)
AS
SELECT @State = [State]
FROM [ExampleTable] 
WHERE [City] = @City
GO;

You can now use this output parameter to pass in a value elsewhere.

For example:

DECLARE @State int
EXEC [dbo].[GetStateFromCity] @City = 'Calgary', @State OUTPUT;

-- Do something with this value
SELECT @State;

-- Do something else
EXEC [dbo].[GetInsuranceCompanyByState] @State;

---

To summarise, if you just want to return a value for a client application, you probably don't need an output parameter.

However, if you want to pass values around in T-SQL between stored procedures, they can be very useful.

For what it's worth, I hardly use output parameters.

Answer 2

Assuming this question pertains to SQL Server: It comes down to context and efficiency.

Context = App Code

When executing the Stored Procedure from app code, it isn't much different in terms of the amount of code. When returning a result set, just call ExecuteReader and then SqlDataReader.Read() to get the one row, and then get the columns from the SqlDataReader. But if you are just getting a single value, then you can use the short-cut method of ExecuteScalar which gets one row (even if there are more rows) and returns the value in the first column (even if there are more columns). When returning OUTPUT parameters, you just need to call ExecuteNonQuery, check the .Value property of each parameter, and cast to the appropriate type.

So, in terms of the simple example of returning a single value, it seems "easiest" to return a result set and call ExecuteScalar. BUT, returning a result set, whether using ExecuteReader or ExecuteScalar, requires more resources of both SQL Server and the client app. SQL Server needs to set up and manage the result set (i.e. requires memory and time), and the app needs to instantiate a SqlDataReader (yes, even when using ExecuteScalar) and manage it (i.e. requires memory and time). If you are guaranteed to only ever have a single result set row, you are better off (even if only slightly) using output parameters.

Context = T-SQL

When executing the Stored Procedure from T-SQL (and needing to use the returned value(s)), it is at least more convenient to use OUTPUT parameters. Returning a result set is usable, but requires inserting the results into a table -- typically a local temporary table or table variable -- using INSERT ... EXEC. But you then still need to select the row into local variables. Again, it is more time and resources to get to the same place of having the values in variables.

Now, when you are returning just a single value (i.e. same situation that works for ExecuteScalar), then you can sometimes use a scalar User-Defined Function (UDF) instead, which has the ability to be placed into a query, which is sometimes very useful (even if there is a performance hit from using Scalar UDFs in queries). However, there are plenty of restrictions on what can be done in UDFs, so if you need to create temp tables, or do any DML or DDL, etc, then using a Stored Procedure is the only option.

---

While not part of the "result set vs OUTPUT parameter" question, it is good to keep in mind that you can do both! If you have some values that are discreet as well as a set of data to return, Stored Procedure do allow for returning both, which on rare occasion, is quite useful.

Answer 3

Two other things I think that are worth noting:

1) You can pass more than one parameter as OUTPUT,

2) You do not have to call the parameters with OUTPUT if you don't want the results

CREATE PROCEDURE ManyOutputs @a int, @b int output, @c varchar(100) output, @d bit output
AS
BEGIN
    SET @b = @a + 11
    SET @c = 'The Value of A is ' + CAST(@a AS varchar(5)) + ', and the value of B is ' + CAST(@b AS varchar(5))
    IF (@a % 2 = 1)
        SET @d = 1
    ELSE
        SET @d = 0
END
GO

Calling this routine:

DECLARE @bVal int
DECLARE @cVal varchar(100)
DECLARE @dVal bit

EXEC ManyOutputs 1, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns NULL, NULL, NULL

EXEC ManyOutputs 2, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 13, "The Value of A is 2, and the value of B is 13", 0

EXEC ManyOutputs 3, @bVal, @cVal, @dVal
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 13, "The Value of A is 2, and the value of B is 13", 0

(the same as the last call, because we didn't get new values by using OUTPUT, so it retained the old values.)

EXEC ManyOutputs 5, @bVal OUT, @cVal OUT, @dVal OUT
SELECT @bVal AS bVal, @cVal as cVal, @dVal as dVal

Returns 16, "The Value of A is 5, and the value of B is 16", 1