How To Use Output Parameter In Stored Procedure In C#

In this code example, we will learn how to create a stored procedure with output parameters and execute in a C# code and return back the values to the caller function.
 
First, we create a table in the database, and also create a stored procedure with an output parameter.
 
Now create a table in the database.
  1. CREATE TABLE [dbo].[tblClients](  
  2.     [fldGuid] [uniqueidentifier] NULL,  
  3.     [fldClientID] [intNULL,  
  4.     [fldClientName] [nvarchar](50) NULL,  
  5.     [fldDateCreated] [datetime] NULL,  
  6.     [fldCellPhone] [nvarchar](30) NULL,  
  7.     [fldEMail] [nvarchar](60) NULL,  
  8.     [fldPK] [intNOT NULL,  
  9.  CONSTRAINT [tblClients_pk] PRIMARY KEY NONCLUSTERED   
  10. (  
  11.     [fldPK] ASC  
  12. )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ONON [PRIMARY]  
  13. )  
Now create a stored procedure with three output parameters.
  1. CREATE PROCEDURE GetClientDetails  
  2. (  
  3.     @ClientID  INT,  
  4.     @Guid VARCHAR(100)   OUTPUT,  
  5.     @ClientName VARCHAR(100)    OUTPUT,  
  6.     @DateCreated DATETIME    OUTPUT  
  7. )  
  8. AS  
  9. BEGIN  
  10.     SELECT @Guid = fldGuid, @ClientName = fldClientName, @DateCreated = fldDateCreated   
  11.     FROM tblClients WHERE fldClientID = @ClientID  
  12. END  
In the above stored procedure, @ClientID is the input parameter and others are the output parameters.
 
In this stored procedure, we get client details using input and output parameters.
 
Now let's call stored procedure from C# code.
 
In app.config/web.config add the following code in <configuration></configuration> section.
  1. <appSettings>  
  2.     <add key="CS" value="server=.;database=myDb;Integrated Security=SSPI;" />  
  3. </appSettings>  
In the code behind, write the following code on a button click event handler or where you see fit.
  1. private void btnShow_Click(object sender, EventArgs e)  
  2. {  
  3.     //Here we pass 1001 as clientid.
  4.     //Now show all three values 
  5.     var getData = StoredProcWithOutPutParameter(1001);
  6.     MessageBox.Show(string.Format("Client guid: {0}, Client Name: {1}, Date: {2}"), clientGuid, clientName, dateCreated);
  7. }  
  1. string clientGuid = "";
  2. string clientName = "";
  3. DateTime dateCreated = null;
  4. private Tuple<string, string, DateTime> StoredProcWithOutPutParameter(int clientId)  
  5. {  
  6.     SqlConnection conn = new SqlConnection();  
  7.     SqlCommand cmd = new SqlCommand();  
  8.     conn.ConnectionString = ConfigurationManager.ConnectionStrings["CS"].ConnectionString;  
  9.     cmd.Connection = conn;  
  10.     cmd.CommandType = CommandType.StoredProcedure;  
  11.     cmd.CommandText = "GetClientDetails";              
  12.     cmd.Parameters.AddWithValue("@ClientID", clientId);  
  13.               
  14.     cmd.Parameters.Add("@Guid", SqlDbType.VarChar, 100);  
  15.     cmd.Parameters["@Guid"].Direction = ParameterDirection.Output;  
  16.     cmd.Parameters.Add("@ClientName", SqlDbType.VarChar, 100);  
  17.     cmd.Parameters["@ClientName"].Direction = ParameterDirection.Output;  
  18.     cmd.Parameters.Add("@DateCreated", SqlDbType.DateTime, 20);  
  19.     cmd.Parameters["@DateCreated"].Direction = ParameterDirection.Output;             
  20.     try  
  21.     {  
  22.         conn.Open();  
  23.         int i = cmd.ExecuteNonQuery();  
  24.         //Storing the output parameters value in 3 different variables.  
  25.         clientGuid = Convert.ToString(cmd.Parameters["@Guid"].Value);  
  26.         clientName = Convert.ToString(cmd.Parameters["@ClientName"].Value);  
  27.         dateCreated = Convert.ToDateTime(cmd.Parameters["@DateCreated"].Value);  
  28.         // Here we get all three values from database in above three variables.  
  29.     }  
  30.     catch (Exception ex)  
  31.     {  
  32.         // throw the exception  
  33.     }  
  34.     finally  
  35.     {  
  36.         conn.Close();  
  37.     }
  38.     return new Tuple<string, string, DateTime>(clientGuid, clientName, dateCreated); 
  39. }

Next Recommended Reading Passing Parameters To A Method In C#