Stored Proc with multiple optional result sets.

[dna]

Hi,

We are having an issue with a complex stored proc. We use spring's Jdbctemplate and StoredProcedure classes.
In database, we have a stored procedure which returns multiple resultset depending on the parameters sent.

eg. storedProc(full) return resultsets a,b,c
storedProc(half) returns only one resultset a.

How can this be implemented in java, without having 2 StoreProc implementations as we have to declare the resultset in the stored proc as shown below. I know we can declare multiple resultsets, but how can we declare multiple optional resultset which may or may not be returned based on inputparam. Right now we are achieving this by having 2 implementations of the storeproc in java. We would like to use a single common one.

public XStoredProc(JdbcTemplate jdbcTemplate) {
super(jdbcTemplate, "procname");
RowMapper<X> xRowMapper = new XRowMapper();
setFunction(true);
declareParameter(new SqlParameter("type", Types.BIT));
declareParameter(new SqlReturnResultSet("RESULT_SET", xRowMapper));
allowsUnusedParameters();
compile();
}

[trisberg]

Any undeclared results returned should be added to the output map with generated names like "#result-set-1" "#result-set-2" etc. You can change this by setting skipUndeclaredResults to true, and then these undeclared resultsets will be skipped.

Sounds like you do want the undeclared result sets, so i would just declare the first one and then look for the others in the output map using these generated names.

Look for an INFO log message saying "Added default SqlReturnResultSet parameter named #result-set-1".