T-SQL stored procedures that return a single a value (scalar) or a single row can benefit from the use of OUTPUT parameters. This is not appropriate for multiple row result sets, but if you just need one value, or the results of a single row, you can do it most efficiently with OUTPUT parameters as opposed to SELECTing into a DataReader or DataSet.
if you just need one value, or the results of a single row, you can do it most efficiently with OUTPUT parameters
The efficiency boost is in ADO.Net, not so much in SQL Server, so don’t bother refactoring your procedures this way if the procedures are not called from your .Net data access layer.
An example stored procedure is below. The fields we want to return to our .Net code are declared as OUTPUT parameters. The WHERE clause criteria (@id) is an input parameter.
And here is a C# example of how to call this stored procedure with ADO.Net:
