I'm currently having an issue with calling a SQL Server 2008 Stored Procedure using JDBC.
The issue appears to be around optional parameters and the order of the parameters in the SProc. The stored proc signature is as follows (names/defaults are changed):
@REQPARAM1 NVARCHAR(50),
@OPTPARAM2 NVARCHAR(50) = N'value',
@OPTPARAM3 NVARCHAR(50) = N'value',
@OPTPARAM4 NVARCHAR(21) = N'value',
@OPTPARAM5 NVARCHAR(50) = N'01234567890',
@OPTPARAM6 NVARCHAR(50) = N'01234567890',
@OPTPARAM7 NVARCHAR(50) = 'testing@test.com',
@OUTPARAM INT OUTPUT
The Java code I am trying to use is
CallableStatement proc = connection.prepareCall("{call ProcName(?,?)}");
proc.setString("REQPARAM1", value);
proc.registerOutParameter("OUTPARAM", java.sql.Types.INTEGER);
proc.execute();
int result = proc.getInt("OUTPARAM");
But this doesn't work, and comes back with
The index 8 is out of range
The only way I seem able to get this to run is by declaring all of the optional parameters, which defeats the object of them being optional. Or by moving the out parameter directly before the optional ones, which creates inconsistency in the database (Code base is primarily .Net, where this is perfectly possible).
So, I guess my question is whether it is possible to use Optional Parameters in this way?
