title: Spring SimpleJdbcCall & SQL Server system Stored Procedure - expects parameter not supplied, but it is?
body: <p>I've googled, I've search StackOverflow, I've read examples and tutorials, I've looked through the Spring JavaDocs and other docs. I've tried a number of different permutations.</p>
  
  <p>Pretty much no matter what I try I get the same or similar error, varying only on which param it complains is not supplied:</p>
  
  <pre><code>org.springframework.jdbc.UncategorizedSQLException: CallableStatementCallback;     
  uncategorized SQLException for SQL [{? = call sys.sp_sequence_get_range(?)}]; 
  SQL state [S0004]; error code [201]; 
  Procedure or function 'sp_sequence_get_range' expects parameter '@range_size',   
  which was not supplied.; nested exception is  
  com.microsoft.sqlserver.jdbc.SQLServerException: Procedure or function 
  'sp_sequence_get_range' expects parameter '@range_size', which was not supplied.
  </code></pre>
  
  <p>Java code:</p>
  
  <pre><code>SimpleJdbcCall jdbcCall = new SimpleJdbcCall(dataSource())
          .withProcedureName("sp_sequence_get_range")
          .withoutProcedureColumnMetaDataAccess()         
          .withCatalogName("sys")
          .declareParameters(new SqlParameter("sequence_name", Types.NVARCHAR), new SqlParameter("range_size", Types.BIGINT))
  
          .withReturnValue();
  
      MapSqlParameterSource params = new MapSqlParameterSource()
          .addValue("sequence_name", "dbo.M_PRODUCT_PROD_ID_SEQ")
          .addValue("range_size", Integer.valueOf(rangeSize_));
  
      Map&lt;String, Object&gt; retVals = jdbcCall.execute(params);
  </code></pre>
  
  <p>The Stored Procedure is the SQL Server 2012 (v 11) sys.sp_sequence_get_range():</p>
  
  <p><a href="https://msdn.microsoft.com/en-us/library/ff878352%28v=sql.110%29.aspx?f=255&amp;MSPPError=-2147217396" rel="nofollow">https://msdn.microsoft.com/en-us/library/ff878352%28v=sql.110%29.aspx?f=255&amp;MSPPError=-2147217396</a></p>
  
  <p>I have a standard sequence defined - I don't think that matters as I can't even get to the SP from my code. From within SQL Server Studio the SP works when I call it like this:</p>
  
  <pre><code>EXEC sys.sp_sequence_get_range
  @sequence_name = N'dbo.M_PRODUCT_PROD_ID_SEQ'
  , @range_size = 500
  , @range_first_value = @FirstSeqNum OUTPUT 
  , @range_last_value = @LastSeqNum OUTPUT  ;
  
  -- The following statement returns the output values
  SELECT
  @FirstSeqNum AS FirstVal
  , @LastSeqNum AS LastVal;
  </code></pre>
  
  <p>I wrote a unit test where rangeSize_ == 100;</p>
  
  <p>The param types match those defined by MS in their docs.</p>
  
  <p>Unless I am blind, the param names match and they are in the same order as the SP expects.</p>
  
  <p>I am using "withoutProcedureColumnMetaDataAccess()" which is the usual suggested remedy for this problem - to no avail.</p>
  
  <p>It seems to get past the first param, but rejects the second?</p>
  
  <p>If I change the params source to include the types:</p>
  
  <pre><code>MapSqlParameterSource params = new MapSqlParameterSource()
          .addValue("sequence_name", "dbo.M_PRODUCT_PROD_ID_SEQ", Types.NVARCHAR)
          .addValue("range_size", BigInteger.valueOf(rangeSize_), Types.BIGINT);
  </code></pre>
  
  <p>I get a different error:</p>
  
  <pre><code>Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set for the parameter number 1.
  </code></pre>
  
  <p>Does this mean I made it further and I just need to register an OUT parameter?</p>
  
  <p>Suggestions?</p>
  
  <p>Thanks in advance.</p>