JPA - Can a stored procedure be called without providing optional parameters?
<p>I'm calling a third party stored procedure with a lot of optional parameters. It's in MS SQL Server 2008 R2 and looks like this:</p>

<pre><code>procedure [dbo].[pTest]
    @ReportDate varbinary(max) out, 
    @Optional1 varchar(max) = null,
    @Optional2 varbinary(max) = null,
    @Date varchar(max) = null,
    -- ~20 more optional params of various types 
as
begin
select * from test
end
</code></pre>

<p>I'm calling it from java using Spring Data JPA 1.10.2 with com.microsoft.sqlserver's sqljdbc 4.2.</p>

<p>I only ever want to call the procedure with two params ReportDate and Date. The rest of the params can stay at the default value (i.e. null).  My code is as follows:</p>

<pre><code>@Entity
@NamedStoredProcedureQuery(name = "Test.get", procedureName = "pTest", parameters = {
@StoredProcedureParameter(mode = ParameterMode.OUT, name = "ReportData", type = byte[].class),          
@StoredProcedureParameter(mode = ParameterMode.IN, name = "Date", type = String.class)

          })
public class Test {

    // serves no purpose other than to meet
    // JPA requirement
    @Id
    private byte[] reportData;
}
</code></pre>

<p>Spring data repository:</p>

<pre><code>public interface TestRepository  extends Repository&lt;Test, Long&gt; {
    @Procedure("pTest")
    byte[] get(@Param("Date") String date);
}
</code></pre>

<p>Test code:</p>

<pre><code>@RunWith(SpringRunner.class)
@SpringBootTest
@Transactional(transactionManager="transactionManager")
public class TestRepositoryTest {

    @Autowired
    private TestRepository testRepository;

    @Test
    public void testGet() throws SQLException {

        byte[] blob = testRepository.get("hi");
        //error occurs
    }

}
</code></pre>

<p>I get the following error though:</p>

<pre><code>   o.h.engine.jdbc.spi.SqlExceptionHelper   : Error preparing CallableStatement [pTest]

com.microsoft.sqlserver.jdbc.SQLServerException: The index 4 is out of range.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:191) ~[sqljdbc-4.2.jar:na]
</code></pre>

<p>I'm getting this because I did not specify Optional1 and Optional2. Now, if I add the Optional1 and Optional2 as <code>@StoredProcedureParameter</code>s and as paramters to getTest method of the <code>TestRepository</code>, I no longer get the error. But then, I have to pass in a non-null values or otherwise I get another error complaining that I passed no value.</p>

<p>How can I call this stored procedure preferably without specifying Optional1 and Optional2? </p>

<p>In some cases, I need to call a stored procedure where the 15th parameter is all I need to set. I really prefer not having to define the other 14 parameters in my code. Even if I did that, I cannot set them to null. </p>