title: java.sql.SQLException: Procedure or function ... expects parameter ..., which was not supplied
body: <p>While executing <code>daoMethod()</code> I am getting the following exception:</p>
  
  <p><strong>java.sql.SQLException: Procedure or function 'Get_Books' expects parameter '@totalRowsReturned', which was not supplied.</strong></p>
  
  <p>Why? I did define <code>@totalRowsReturned</code> as OUTPUT. And I do not understand why I am required to supply <code>@totalRowsReturned</code> - it is an output parameter, not input.</p>
  
  <p>Dao Class:</p>
  
  <pre><code>public class BookDao {
  
      @Autowired
      DataSource dataSource;
  
      public void daoMethod() {
  
          Integer programIdLocal = null;
  
          Map&lt;String, Object&gt; parameters = new HashMap&lt;String, Object&gt;();
          parameters.put("bookId", 1);
  
          MyStoredProcedure storedProcedure = new MyStoredProcedure(dataSource);      
  
          //Exception!!!!
          Map&lt;String, Object&gt; results = storedProcedure.execute(parameters);
  
      }
  
      private class MyStoredProcedure extends StoredProcedure {
  
          private static final String SQL = "dbo.Get_Books";
  
          public MyStoredProcedure(DataSource dataSource) {
              setDataSource(dataSource);
              setFunction(true);
              setSql(SQL);
  
              declareParameter(new SqlReturnResultSet("rs", new BookMapper()));
  
              declareParameter(new SqlOutParameter("totalRowsReturned", Types.INTEGER));
  
              declareParameter(new SqlParameter("bookId", Types.INTEGER));
  
              setFunction(true);
  
              compile();
          }
  
      }   
  }
  </code></pre>
  
  <p>Stored Procedure:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[Get_Books]
  
      @bookId int,
      @totalRowsReturned int OUTPUT
  
  AS
  
  BEGIN
  
    SET NOCOUNT ON;
    DECLARE @SelectQuery NVARCHAR(2000)
  
    DECLARE @first_id int
    DECLARE @totalRows int
  
    SET @SelectQuery = 'FROM books b WHERE b.book_id &gt;= @bookId'
  
    Set @SelectQuery = 'SELECT @first_id = b.book_id , @totalRows=Count(*) OVER() ' + @SelectQuery + ' ORDER BY b.book_id'
    Execute sp_Executesql @SelectQuery, N'@first_id int, @bookId int, @totalRows int OUTPUT', @first_id, @bookId, @totalRows=@totalRowsReturned OUTPUT
  
  END
  </code></pre>