Call stored procedure specifying only parameters with a value

Question

In an instance of SQL Server 2016 I have a stored procedure with dozens of parameters. For example:

CREATE PROCEDURE spName (
    @par1 INT = NULL,
    @par2 VARCHAR(10) = NULL,
        ....
        ....
    @par98 INT = NULL,
    @par99 INT = NULL,
) AS
BEGIN
    ....
    ....
END

I have a client written in C# that calls the stored procedure specifying only the parameters with a value. Ex:

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "spName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = dbConn;

cmd.Parameters.Add(new SqlParameter("par1", "val1"));
cmd.Parameters.Add(new SqlParameter("par47", "val47"));

...

cmd.ExecuteNonQuery();

It works perfectly! So, the procedure is executed and only the 2 parameters (par1 and par47) have a value. Other parameters maintain the default value (NULL).

I would do the same from a Java client using Microsoft JDBC driver 6.2. I specify the parameters with List<Map<String, Object>>, so a list of couple parameterName-->parameterValue. The following method builds the PreparedStatement object:

private CallableStatement prepareStatement(String spName, Map<String, ?> parameters) throws SQLException {
    setupConnection();
    CallableStatement stmt = null;
    try {
        stmt = conn.prepareCall(getSpCallString(spName, parameters));
        if (parameters != null) {
            for (String parName : parameters.keySet())
                stmt.setObject(parName, parameters.get(parName));
        }
    } catch (SQLException e) {
        ApplicationLogging.severe("Cannot prepare callable statement", e);
        throw e;
    }
    return stmt;
}

The method getSpCallString() generates a string of the type { call spName ?,?, ... , ? } with a number of ? as the number of parameters with a value passed to the procedure, so not all 99 parameters. If I have 2 parameter it generates the string { call spName ?,? }. By passing for example par15=val15 and par47=val47 it raises the following exception:

com.microsoft.sqlserver.jdbc.SQLServerException: The index 2 is out of range.

I could resolve this putting in the call command the same number of ? as the number of parameter of the stored procedure but... I don't know the number of parameters for each stored procedure (and their position)! In C# this is simply resolved because the parameters are assigned only with their name, so the number and the order of parameters can be really a black box.

Can I do this in some way in Java?

Answer 1

This is a confirmed deficiency in the current implementation of named parameter support for CallableStatement in the mssql-jdbc driver. Despite section 13.3.2 of the JDBC 4.2 specification stating ...

Named parameters can be used to specify only the values that have no default value.

... we seem to be required to provide a parameter placeholder for every possible parameter, and there doesn't appear to be a way to specify DEFAULT for the parameters we might otherwise simply omit.

As a workaround we could use code like this

public static ResultSet executeStoredProcedureQuery(
        Connection conn, String spName, Map<String, Object> paramItems) 
        throws SQLException {
    StringBuffer sqlBuf = new StringBuffer("EXEC ");
    sqlBuf.append(spName);
    int paramCount = 1;
    for (String paramName : paramItems.keySet()) {
        sqlBuf.append(
                (paramCount++ > 1 ? ", " : " ") + 
                (paramName.startsWith("@") ? "" : "@") + paramName + "=?");
    }
    String sql = sqlBuf.toString();
    myLogger.log(Level.INFO, sql);
    // e.g., EXEC dbo.BreakfastSP @helpings=?, @person=?, @food=?
    PreparedStatement ps = conn.prepareStatement(sql);
    paramCount = 1;
    for (String paramName : paramItems.keySet()) {
        ps.setObject(paramCount++, paramItems.get(paramName));
    }
    return ps.executeQuery();
}

which we could call like this

// test data
Map<String, Object> paramItems = new HashMap<>();
paramItems.put("@person", "Gord");
paramItems.put("@food", "bacon");
paramItems.put("@helpings", 3);
//
ResultSet rs = executeStoredProcedureQuery(conn, "dbo.BreakfastSP", paramItems);

Answer 2

If using a third party library to facilitate calling such procedures is an option for you, then jOOQ certainly helps via its code generator for stored procedures, which generates stubs for each of your procedures, making such calls type safe. It includes support for:

• Table valued functions
• Table valued parameters
• Defaulted parameters
• In / Out parameters
• Optional return value of procedures
• Fetching undeclared update counts and result sets
• Much more

In your case, you could write:

Spname sp = new Spname();
sp.setPar1("val1");
sp.setPar47("val47");
sp.execute(configuration); // The object containing your JDBC connection
sp.getResults();           // The result set(s) and update counts, if any

Behind the scenes, a JDBC CallableStatement is created, just like you would do manually:

try (CallableStatement s = c.prepareCall(
    "{ ? = call [dbo].[spName] (@par1 = ?, @par47 = ?) }"
)) {
    // Get the optional procedure return value that all procedures might return
    s.registerOutParameter(1, Types.INTEGER); 

    s.setString(2, "val1");
    s.setString(3, "val47");
    s.execute();
    // Lengthy procedure to fetch update counts and result set(s)
}

See this article if you want to generically fetch update counts and result set(s) with JDBC.

Disclaimer: I work for the company behind jOOQ.