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.

	/*
	Unit Test:

	DECLARE @id int;
	DECLARE @FruitName_OUT NVARCHAR(50);
	DECLARE @FruitColor_OUT NVARCHAR(50);
	DECLARE @FruitGrowsOn_OUT int;
	DECLARE @FruitIsYummy_OUT bit;

	EXECUTE dbo.[GetFruitByID] 1, @FruitName_OUT OUTPUT, @FruitColor_OUT OUTPUT, @FruitGrowsOn_OUT OUTPUT, @FruitIsYummy_OUT OUTPUT

	PRINT @FruitName_OUT;
	PRINT @FruitColor_OUT;
	PRINT @FruitGrowsOn_OUT;
	PRINT @FruitIsYummy_OUT;

	*/
	CREATE PROCEDURE [dbo].[GetFruitByID]
	@id int,
	@FruitName NVARCHAR(50) OUTPUT,
	@FruitColor NVARCHAR(50) OUTPUT,
	@FruitGrowsOn int OUTPUT,
	@FruitIsYummy bit OUTPUT
	AS
	SET NOCOUNT ON;

	SELECT @FruitName = [FruitName], @FruitColor = [FruitColor], @FruitGrowsOn = [FruitGrowsOn], @FruitIsYummy = [FruitIsYummy]
	FROM [dbo].[Fruit]
	WHERE Id = @id;

	RETURN 0

view raw

TSQLOutput

hosted with ❤ by GitHub

And here is a C# example of how to call this stored procedure with ADO.Net:

	using System;

	namespace OutputParamsInCSharp
	{
	class Program
	{
	static void Main(string[] args)
	{
	System.Data.SqlClient.SqlConnectionStringBuilder connectionBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
	connectionBuilder.DataSource = "(localdb)\\v11.0";
	connectionBuilder.InitialCatalog = "Fruit";
	connectionBuilder.IntegratedSecurity = true;

	using (System.Data.SqlClient.SqlConnection sqlConn = new System.Data.SqlClient.SqlConnection(connectionBuilder.ConnectionString))
	{
	using (System.Data.SqlClient.SqlCommand sqlCmd = new System.Data.SqlClient.SqlCommand("GetFruitByID", sqlConn))
	{
	sqlCmd.CommandType = System.Data.CommandType.StoredProcedure;
	sqlConn.Open();

	System.Data.SqlClient.SqlParameter paramId = new System.Data.SqlClient.SqlParameter("@id", System.Data.SqlDbType.Int);
	paramId.Direction = System.Data.ParameterDirection.Input;
	paramId.Value = 1;
	sqlCmd.Parameters.Add(paramId);

	System.Data.SqlClient.SqlParameter paramFruitName = new System.Data.SqlClient.SqlParameter("@FruitName", System.Data.SqlDbType.NVarChar);
	paramFruitName.Direction = System.Data.ParameterDirection.Output;
	paramFruitName.Size = 50;
	sqlCmd.Parameters.Add(paramFruitName);

	System.Data.SqlClient.SqlParameter paramFruitColor = new System.Data.SqlClient.SqlParameter("@FruitColor", System.Data.SqlDbType.NVarChar);
	paramFruitColor.Direction = System.Data.ParameterDirection.Output;
	paramFruitColor.Size = 50;
	sqlCmd.Parameters.Add(paramFruitColor);

	System.Data.SqlClient.SqlParameter paramFruitGrowsOn = new System.Data.SqlClient.SqlParameter("@FruitGrowsOn", System.Data.SqlDbType.Int);
	paramFruitGrowsOn.Direction = System.Data.ParameterDirection.Output;
	sqlCmd.Parameters.Add(paramFruitGrowsOn);

	System.Data.SqlClient.SqlParameter paramFruitIsYummy = new System.Data.SqlClient.SqlParameter("@FruitIsYummy", System.Data.SqlDbType.Bit);
	paramFruitIsYummy.Direction = System.Data.ParameterDirection.Output;
	sqlCmd.Parameters.Add(paramFruitIsYummy);

	sqlCmd.ExecuteNonQuery();

	Console.WriteLine(paramFruitName.Value);
	Console.WriteLine(paramFruitColor.Value);
	Console.WriteLine(paramFruitGrowsOn.Value);
	Console.WriteLine(paramFruitIsYummy.Value);

	Console.ReadKey();

	}
	}
	}
	}
	}

view raw

OutputParamsCSharp

hosted with ❤ by GitHub

contrived example

contrived examples of useful code

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

Leave a Reply Cancel reply

Share this:

Like this:

Related

Leave a Reply Cancel reply