title: Stored Procedures Basic Help Getting Totals Back From table
body: <p>This is my stored procedure, the query returns a table with three columns with a total amount within each column.</p>
  
  <p>UnitPrice, Tax, OrderTotal</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_TaxOrderReport]
      -- Add the parameters for the stored procedure here
      @StartDate date,
      @EndDate date,
      @TaxReturnValue int,
      @TotalReturnValue int,
      @UnitReturnValue int
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
  
      -- Insert statements for procedure here
      SELECT Sum(oi.UnitPrice) as UnitPrice, Sum(oi.Tax) as Tax,
          Sum(oi.TotalAmount) as OrderTotal
       FROM OrderItems oi, Orders o
       where @StartDate &lt;= o.OrderDate and O.OrderDate &lt;= @EndDate 
          and o.OrderId = oi.OrderId
  
      select @TaxReturnValue as Tax
      select @UnitReturnValue as UnitPrice
      select @TotalReturnValue as OrderTotal
  
      -- How can I return several values over?? 
      return @TaxReturnValue
  
  END
  </code></pre>
  
  <p>My goal is to have an admin input dates and get a report back of total sales tax and money before tax generated for him. This is what my method looks like in my controller.</p>
  
  <pre><code>public ActionResult GetTaxReport(string StartDate, string EndDate)
  {
      string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
  
      using (var connection = new SqlConnection(connectionString))
      {
          connection.Open();
          using (SqlCommand cmd = new SqlCommand("sp_TaxOrderReport", connection))
          {
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate;
              cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate;
              cmd.Parameters.Add("@TaxReturnValue", SqlDbType.Int).Value = 0;
              cmd.Parameters.Add("@UnitReturnValue", SqlDbType.Int).Value = 0;
              cmd.Parameters.Add("@TotalReturnValue", SqlDbType.Int).Value = 0;
  
              cmd.ExecuteNonQuery();
              var returnvalue = cmd.Parameters["@TaxReturnValue"].Value.ToString();
          }
          connection.Close();
      }
      return View("FindUser");
  }
  </code></pre>
  
  <p>The @TaxReturnValue is not getting set to the value from the column, I cant seem to find a way of setting all three of these variables and then returning them all so I can render them in my view. If anyone has any tips i would appreciate it.</p>
  
  <pre><code>-----UPDATE--------------
  </code></pre>
  
  <p>Thought I would update the question to contain the answer just in case anyone needs a very explicit answer regarding how to return and set variables passed in to a stored procedure.</p>
  
  <p>Stored Procedure:</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[sp_TaxOrderReport]
      -- Add the parameters for the stored procedure here
      @StartDate date,
      @EndDate date,
      @TaxReturnValue int OUTPUT,
      @TotalRevenue int OUTPUT,
      @UnitReturnValue int OUTPUT
  AS
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      --Insert statements for procedure here
      SELECT
          @TaxReturnValue = Sum(oi.Tax), 
          @UnitReturnValue = Sum(oi.UnitPrice),
          @TotalRevenue = Sum(oi.TotalAmount)
       FROM OrderItems oi, Orders o
       where @StartDate &lt;= o.OrderDate and O.OrderDate &lt;= @EndDate 
          and o.OrderId = oi.OrderId
  
  END
  </code></pre>
  
  <p>C# Code:</p>
  
  <pre><code>        string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
  
          using (var connection = new SqlConnection(connectionString))
          {
              connection.Open();
              using (SqlCommand cmd = new SqlCommand("sp_TaxOrderReport", connection))
              {
                  cmd.CommandType = CommandType.StoredProcedure;
                  cmd.Parameters.Add("@StartDate", SqlDbType.DateTime).Value = StartDate;
                  cmd.Parameters.Add("@EndDate", SqlDbType.DateTime).Value = EndDate;
                  cmd.Parameters.Add("@TaxReturnValue", SqlDbType.Int).Value = 0;
                  cmd.Parameters.Add("@UnitReturnValue", SqlDbType.Int).Value = 0;
                  cmd.Parameters.Add("@TotalRevenue", SqlDbType.Int).Value = 0;
  
  
                  cmd.Parameters["@TaxReturnValue"].Direction = ParameterDirection.Output;
                  cmd.Parameters["@TotalRevenue"].Direction = ParameterDirection.Output;
                  cmd.Parameters["@UnitReturnValue"].Direction = ParameterDirection.Output;
  
                  cmd.ExecuteNonQuery();
  
                  var taxTotal = cmd.Parameters["@TaxReturnValue"].Value;
                  var unitPriceTotal = cmd.Parameters["@UnitReturnValue"].Value;
                  var totalRenuve = cmd.Parameters["@TotalRevenue"].Value;
  
              }
              connection.Close();
          }
  </code></pre>
  
  <p>Thanks for all the help guys! </p>