title: Multiple stored procedures with input and output parameters called from java
body: <p>I want to add a new row in a table - <code>NewOrder</code> which has the columns <code>ID, OrderDate, OrderValue</code> using the following stored procedure.</p>
  
  <pre><code>create proc [dbo].[insert_new_order](   @newID int OUTPUT,
                      @oValue float)
  as
  begin
  insert into NewOrder values (GETDATE(),@oValue)
  set @newID = @@IDENTITY
  end
  </code></pre>
  
  <p>The <code>@newID</code> represents one of the input parameters for the following stored procedure which inserts a new row in the table <code>OrderedProduct</code> which has the columns <code>ProductID, OrderID, Price, Quantity</code></p>
  
  <pre><code>   create proc [dbo].[insert_updates_in_ordered_product] (  @newID int,
                                                              @productID int,
                                                              @price float,   
                                                              @qty int)
      as
      begin
      insert into OrderedProduct values(@productID,@newID,@qty,@price)
      end
  </code></pre>
  
  <p>I am calling these 2 stored procedures like this:</p>
  
  <pre><code>     public static void addNewOrderToDB(ArrayList&lt;Product&gt; list){
          Connection connection = null;
          CallableStatement statement1 = null;
          String q1 = "{? = CALL insert_new_order(?)}";
          String q2 = "{CALL insert_updates_in_ordered_product(?,?,?,?)}";
          float orderValue = 0;
          //calculate orderValue
  
          for(Product p : list){
              orderValue = orderValue + (p.getPrice() * p.getQty());              
          }
          System.out.println(orderValue);
  
          try {
              Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver").newInstance();
              connection = DriverManager.getConnection(url);
              statement1 = connection.prepareCall(q1);
              statement1.registerOutParameter(1, Types.INTEGER);
              statement1.setFloat(2, orderValue); 
  
              ResultSet rs = statement1.executeQuery();
              int uniqueID = rs.getInt(1);
              System.out.println(uniqueID);
              statement1 = connection.prepareCall(q2);
  
              for(Product p : list){
                  statement1.setInt(1, p.getProductId());
                  statement1.setInt(2,uniqueID);
                  statement1.setInt(3, p.getQty());
                  statement1.setFloat(4, p.getPrice());
              }               
              statement1.executeUpdate();
  
          } catch (Exception e) {
              e.printStackTrace();
          }  finally{
              if(statement1 != null){
                  try {
                      statement1.close();
                  } catch (SQLException e) {
                      System.err.println("SQLException: " + e.getMessage());
                  }
              }
  
              if(connection != null){
                  try {
                      connection.close();
                  } catch (SQLException e) {
                      System.err.println("SQLException: " + e.getMessage());
                  }
              }           
  
          }
   }
  </code></pre>
  
  <p>But i get this error:</p>
  
  <pre><code>com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 'insert_new_order' expects parameter '@oValue', which was not supplied.
  </code></pre>
  
  <p>How can i fix this?</p>