Procedures

Stored procedures are procedural program code that contains one or more SQL statements and program logic.

Stored procedures are stored in the database and executed with a call from the application or another stored procedure.

An application can call a procedure in place of an SQL statement. ODBC uses the following escape clause for calling a procedure:

where procedure-name specifies the name of a procedure that is stored on the data source and any parameter values specify the procedure parameters.

Note solidDB does not support the optional "?=" that is included in the ODBC standard:

▪ For input and input/output parameters, parameter can be a literal or a parameter marker. Because some data sources do not accept literal parameter values, be sure that interoperable applications use parameter markers.

▪ For output parameters, parameter must be a parameter marker.

If a procedure call includes parameter markers, the application must bind each marker by calling SQLBindParameter() prior to calling the procedure.

Procedure calls do not require input and input/output parameters, however, the following rules apply:

▪ A procedure that is called with parentheses but with parameters omitted, such as {call procedure_name()} might cause the procedure to fail.

▪ A procedure called without parentheses, such as {call procedure_name}, returns no parameter values.

▪ Input parameters can be omitted. Omitted input or input/output parameters cause the driver to instruct the data source to use the default value of the parameter. As an option, a parameter default value can be set by setting the value of the length/indicator buffer (that is bound to the parameter) to SQL_DEFAULT_PARAM.

▪ When a parameter is omitted, the comma (that delimits the missing parameter from other parameters) must be included.

▪ Omitted input/output parameters or literal parameter values cause the driver to discard the output value.

▪ Omitted parameter markers for a procedure return value cause the driver to discard the return value.

▪ If an application specifies a return value parameter for a procedure that does not return a value, the driver sets the value of the length/indicator buffer (that is bound to the parameter) to SQL_NULL_DATA.

To determine if a data source supports procedures, an application calls SQLGetInfo() with the SQL_PROCEDURES information type.