title: Storing query results into a variable and modifying it inside a Stored Procedure
body: <p>I have a website where customer order stuff online, in a cart model etc.</p>
  
  <p>The problems is when I knew order is recorded there are different information that needs to be updated like entry in order table, deductions from stock table, updating sales table etc. and I currently I am doing this by running each single query one time to the database getting its result modifying as per requirement and then run another query to update the result, as the next query requires information from the previous one:</p>
  
  <pre><code>        bool stts = false;
          int re1 = 0, re2 = 0, re3 = 0;
          short maxOr = 0, maxCa = 0, maxOc = 0;
          SqlConnection conn= Shared.GetSqlCon(); //Make connection object
  
          conn= Shared.GetSqlCon();
          var comm1 = new Commmand("SELECT MAX(orId) FROM [order];", sqlCon);
          maxOr = Shared.OSC(sqlCon, comm1);
          Shared.COC(sqlCon); //Close &amp; Dispose connections
  
          conn= Shared.GetSqlCon();
          var comm2 = new Commmand("SELECT MAX(caId) FROM [cart];", sqlCon);
          maxCa = Shared.OSC(sqlCon, comm2);
          Shared.COC(sqlCon);
  
          conn= Shared.GetSqlCon();
          var comm3 = new Commmand("INSERT INTO [order_cart](orId,caId) VALUES(@maxOr,@maxCa);", sqlCon);
          comm3.Parameters.AddWithValue("@maxOr", maxOr + 1);
          comm3.Parameters.AddWithValue("@maxCa", maxCa + 1);
  </code></pre>
  
  <p>And of course this in any way is not a great way to do it going back and forth to database again and again and I think going it through SQL Server <code>Stored Procedures</code> would be a better idea. But even after trying and finding a lot I couldn’t find an example of how can store the result of a query in a <code>SP</code> variable and use it inside it, somewhat like this:</p>
  
  <pre><code>Declare @myVar int //Stored Procedure variable
  @myVar = SELECT MAX(caId) FROM [cart] //Getting query result in the variable
  INSERT INTO [order_cart](orId,caId) VALUES(@maxOr, @myVar); //Updating record through the variable
  return @myVar //return variable value to the program
  </code></pre>
  
  <p>Is this possible to do this? If yes than how please guide.</p>