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 |
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(); | |
| } | |
| } | |
| } | |
| } | |
| } |
