OUTPUT parameters are the most efficient way to retrieve a single row from a Stored Procedure

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:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s