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?


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

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

3 Answers 3


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)
SELECT @State = [State]
FROM [ExampleTable] 
WHERE [City] = @City

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.


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.

  • From Appcode i wonder how to handle Updates and inserts for records using stored procedures. We are facing a problem with Entity Framework that an update sometimes fails so we are switching to dapper and using a stored procedure. How would you handle if a an update was successfull. For example return the just updated record (Update first, then SELECT) or an output parameter (Success = 1 = true) or both?
    – surfmuggle
    Commented Sep 11, 2023 at 6:18
  • @surfmuggle Why are you switching to Dapper when EF can handle calling stored procedures? Why do you need to call a stored procedure in the first place just because an update fails? Can't you trap the SqlException? Regarding handling success of a stored procedure, typically no error is assumed to be success, else an exception is raised, either via RAISERROR or THROW. Commented Sep 21, 2023 at 16:00
  • The initial point was that i could not find the cause of the bug (worked on my machine, did not work on the server). While having trouble with EF i watched this Advisement / Consideration about EF and came to the same conclusion for my case.
    – surfmuggle
    Commented Sep 22, 2023 at 9:50

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
    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
        SET @d = 0

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


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

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.