title: JDBC SQL Server : The value is not set for the parameter number
body: <p>I received the following error from the code that calls a stored procedure from java code:    </p>
  
  <blockquote>
    <p>Exception Trace {} org.springframework.jdbc.UncategorizedSQLException:
    CallableStatementCallback; uncategorized SQLException for SQL [{call
    test.usp_xxx_GetCompanyDetails(?, ?, ?, ?, ?, , ?, ,
    ?, ?, ?, ?, ?)}]; SQL state [null]; error code [0]; The value is not
    set for the parameter number 11.; nested exception is
    com.microsoft.sqlserver.jdbc.SQLServerException: The value is not set
    for the parameter number 11. at
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:84)
    at
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at
    org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at
    org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:1095)
    at
    org.springframework.jdbc.core.JdbcTemplate.call(JdbcTemplate.java:1131)</p>
  </blockquote>
  
  <p>The application is deployed on WAS 8.5.5 and using jdbc driver version 4.2. On restarting the server this issue did not occur again. The following call statement generated looks to be incorrect. There are consecutive commas without ? between them.</p>
  
  <blockquote>
    <p>{call test.usp_xxx_GetCompanyDetails(?, ?, ?, ?, ?, , ?, , ?, ?, ?, ?,
    ?)}</p>
  </blockquote>
  
  <p>The stored procedure has 10 parameters. Following is the definition of the stored procedure:</p>
  
  <pre><code>CREATE PROCEDURE [test].[usp_xxx_GetCompanyDetails]
  (
      @ANumber        int,
      @CompanyId      int,
      @UserRole       varchar(15),
      @RequestId      varchar(100),
      @CompanyCode    varchar(5),
      @BaseSystem     varchar(5),
      @PType          varchar(20),    
      @PId            varchar(40),
      @IsActive       bit,     
      @responseData   xml OUT
  )
  </code></pre>
  
  <p>Following is the java code that makes a call to the stored proc. It is using spring data to make the call.</p>
  
  <pre><code> private String executeProc(Integer aNumber,Integer companyId, String baseSystem, 
                  String role,String companyCode,String requestId, String pType,String pId,
                  boolean isActive ) throws SQLException {
  
              SQLXML responseData=null;
              Map&lt;String,Object&gt; inputParams= new HashMap&lt;&gt;();
              inputParams.put("ANumber", aNumber);
              inputParams.put("CompanyId", companyId);
              inputParams.put("UserRole", role);
              inputParams.put("RequestId", requestId);
              inputParams.put("CompanyCode", companyCode);
              inputParams.put("BaseSystem", baseSystem);
              inputParams.put("PType", pType);
              inputParams.put("PId", pId);
              inputParams.put("IsActive", isActive);
              inputParams.put("ResponseData", responseData);
  
              Map&lt;String, Object&gt; result = this.execute(inputParams);
              String responseXMLString = ((SQLXML) result.get("ResponseData")).getString();
  
              return responseXMLString;
          }
  </code></pre>
  
  <p>What could have gone wrong.</p>