Optional Stored Procedure Parameters

[markaddleman]

I'm using Teiid 7.3 and want to create a stored procedure with optional paramters.  I can't get the metadata to recognize that they are optional.  Is the following correct:

            final ProcedureParameter parameter =

                    metadataFactory.addProcedureParameter(parameterName,

                                                          TypeFacility.getDataTypeName(String.class),

                                                          Type.In,

                                                          procedure);

            parameter.setOptional(true);

Whenever I try to call the stored procedure without the parameters, I get an error indicating the parameters are required.

[markaddleman]

As it turns out, you also have to specify parameter.setDefaultValue(...). 

This leads me to my next question.

If I call the stored procedure using the form "CALL stored_proc()" then the stored procedure gets called with the default value.  However, if invoke the stored procedure thusly "SELECT * FROM stored_proc" Teiid complains that "no valid criteria specified for parameter..." 

Should both forms work?

[shawkins]

Mark,

As you have seen setting the default value will work, or setting nullable will allow the parameter to use a default value. 

Procedure relational selects are mimicing table semantics, not procedure semantics.  With SELECT * FROM stored_proc you are logically asking for the entire domain/range of results from the procedure.  In general this is not possible to determine.  With additional metadata and constrained input value domains we could in theory do the evaluation.

Intermixing the relational and procedural paradigms with default parameters can be confusing.  A possible enhancement would be alternative syntax that would make the intent more clear to execute as a procedure, such as SELECT * FROM stored_proc(), SELECT * FROM stored_proc WITH DEFAULTS, etc.

Steve

[markaddleman]

Thanks, Steve.