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?

Measured

What is a valorized parameter? I am not familiar with that term. – Barcroft

I mean a parameter that I give to the stored procedure (for which there is a value). Other parameters are not passed, so they maintain their default value (NULL). – Measured

You can't, not without generating a SQL Server specific stored procedure call that explicitly assigns values to named parameters. – Bachelorism

I think you mean you want to use optional parameters? You have to use named parameters for this to work and you would have to assign a default value (which can be NULL) in the procedure definition. But how can you not know how many parameters are in a stored procedure? If you don't know the parameters perhaps you should consider not calling that procedure. Or at least spending a little time to look at the procedure so you know what you are doing. – Barcroft

Hi Sean. I have a stored procedure that extracts data filtering by many parameters. Then I have various user interfaces. All of them call the same stored procedure but each one has different parameters to pass to the store procedure (a subset of the parameters). When the client request is processed, only parameters coming from the calling view are passed to the stored procedure, not all. – Measured

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);

Borreri

Thank you Gord. I also thought that is a "bug" or missing feature of the MS Jdbs drivers. – Measured

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.

Blench

Thank you Lukas. This approach is exactly what I did in the library I made to solve this problem. – Measured

@ArmandoContestabile: Did you open source yours? – Blench

Sorry it's for internal use for the company where I'm working. – Measured