title: How to return multiple output parameters from stored procedure for C# function
body: <p>I'm using output parameters to get values from my database.</p>
  
  <p>This is my stored procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_GetCustomerMainData] 
      -- Add the parameters for the stored procedure here
          @Reference nvarchar(100),
          @SubscriptionPIN nvarchar(100) OUTPUT,
          @SignupDate nvarchar(100) OUTPUT,
          @ProductCount int OUTPUT
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      SET @SubscriptionPIN = 'N/A'
      SET @SignupDate = 'N/A'
      SET @ProductCount = 0
  
      -- Insert statements for procedure here
      IF EXISTS(SELECT [SubscriptionPIN] FROM [Norton].[dbo].[Customers] WHERE [Reference] = @Reference)
      BEGIN
          SELECT TOP 1 @SubscriptionPIN = [SubscriptionPIN], @SignupDate = SignUpDate  FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference
          SET @ProductCount = (SELECT COUNT(*) FROM [Norton].[dbo].[ProductList] WHERE [Reference] = @Reference)
      END
  
      RETURN (@SubscriptionPIN)
      RETURN (@SignupDate)
      RETURN (@ProductCount)
  END
  </code></pre>
  
  <p>I'm not sure about the returns at the end:</p>
  
  <pre><code>RETURN (@SubscriptionPIN)
  RETURN (@SignupDate)
  RETURN (@ProductCount)
  </code></pre>
  
  <p>On the other side, here is the c# code :</p>
  
  <pre><code>using (SqlConnection con = new SqlConnection(connectionInfo))
  {
      using (SqlCommand cmd = new SqlCommand("sp_GetCustomerMainData", con) { CommandType = CommandType.StoredProcedure })
      {
          cmd.Parameters.Add("@Reference", SqlDbType.NVarChar).Value = CustomerReferenceID;
  
          SqlParameter SubscriptionPIN = new SqlParameter("@TheCustomerID", SqlDbType.NVarChar) { Direction = ParameterDirection.Output };
          cmd.Parameters.Add(SubscriptionPIN);
  
          SqlParameter SignupDate = new SqlParameter("@SignupDate", SqlDbType.NVarChar) { Direction = ParameterDirection.Output };
          cmd.Parameters.Add(SignupDate);
  
          SqlParameter ProductCount = new SqlParameter("@ProductCount", SqlDbType.Int) { Direction = ParameterDirection.Output };
          cmd.Parameters.Add(ProductCount);
  
          con.Open();
  
          try
          {
              cmd.ExecuteNonQuery();
  
              if (cmd.Parameters["@TheCustomerID"].Value.ToString() != "N/A")
              {
                  aStatus.SubscriptionPIN = cmd.Parameters["@TheCustomerID"].Value.ToString();
                  aStatus.SignupDate = cmd.Parameters["@SignupDate"].Value.ToString();
                  aStatus.ProductCount = int.Parse(cmd.Parameters["@ProductCount"].Value.ToString());
                  aStatus.Result = "0: Reference ID Found";
               }
               else
               {
                   aStatus.Result = "1: Reference ID does not exists";
                   return aStatus;
               }
            }
            catch (SqlException sqlExc)
            {
                foreach (SqlError error in sqlExc.Errors)
                {
                    aStatus.Result = string.Format("{0}: {1}", error.Number, error.Message);
                    return aStatus;
                }
            }
        }
  }
  </code></pre>
  
  <p>When I run this code, I get error:</p>
  
  <blockquote>
    <p>System.InvalidOperationException: String[1]: the Size property has an invalid size of 0.<br>
    at System.Data.SqlClient.SqlParameter.Validate(Int32 index, Boolean isCommandProc)<br>
    at System.Data.SqlClient.SqlCommand.SetUpRPCParameters(_SqlRPC rpc, Int32 startCount, Boolean inSchema, SqlParameterCollection
    parameters)<br>
    at System.Data.SqlClient.SqlCommand.BuildRPC(Boolean inSchema, SqlParameterCollection parameters, _SqlRPC&amp; rpc)<br>
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
    async)<br>
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
    cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
    method, DbAsyncResult result)<br>
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult
    result, String methodName, Boolean sendToPipe)<br>
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()</p>
  </blockquote>
  
  <p>I don't know what is the correct way to send many output parameters from stored procedure, can someone help please?</p>