Procedure Calls

A procedure is an executable object stored on the data source. Generally, it is one or more SQL statements that have been precompiled. The escape sequence for calling a procedure is:

{[?=]call procedure-name[([parameter][,[parameter]]...)]}

where procedure-name specifies the name of a procedure and parameter specifies a procedure parameter.

For more information on the procedure call escape sequence, see “Procedure Call Escape Sequence” in Appendix C, “SQL Grammar.”

A procedure can have zero or more parameters. It can also return a value, as indicated by the optional parameter marker ?= at the start of the syntax. If parameter is an input or an input/output parameter, it can be a literal or a parameter marker. However, interoperable applications should always use parameter markers, as some data sources do not accept literal parameter values. If parameter is an output parameter, it must be a parameter marker. Parameter markers must be bound with SQLBindParameter before the procedure call statement is executed.

Input and input/output parameters can be omitted from procedure calls. If a procedure is called with parentheses but without any parameters, such as {call procedure-name()}, the driver instructs the data source to use the default value for the first parameter. If the procedure does not have any parameters, this may cause the procedure to fail. If a procedure is called without parentheses, such as {call procedure-name}, the driver does not send any parameter values.

Literals can be specified for input and input/output parameters in procedure calls. For example, suppose the procedure InsertOrder has five input parameters. The following call to InsertOrder omits the first parameter, provides a literal for the second parameter, and uses a parameter marker for the third, fourth, and fifth parameters:

{call InsertOrder(, 10, ?, ?, ?)} // Not interoperable!

Note that if a parameter is omitted, the comma delimiting it from other parameters must still appear. If an input or input/output parameter is omitted, the procedure uses the default value of the parameter. Another way to specify the default value of an input or input/output parameter is to set the value of the length/indicator buffer bound to the parameter to SQL_DEFAULT_PARAM.

If an input/output parameter is omitted or if a literal is supplied for the parameter, the driver discards the output value. Similarly, if the parameter marker for the return value of a procedure is omitted, the driver discards the return value. Finally, 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 bound to the parameter to SQL_NULL_DATA.

Suppose the procedure PARTS_IN_ORDERS creates a result set containing a list of orders which contain a particular part number. The following code calls this procedure for part number 544:

SQLUINTEGER PartID;

SQLINTEGER PartIDInd = 0;

// Bind the parameter.

SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_SLONG, SQL_INTEGER, 0, 0,

&PartID, 0, PartIDInd);

// Place the department number in PartID.

PartID = 544;

// Execute the statement.

SQLExecDirect(hstmt, "{call PARTS_IN_ORDERS(?)}", SQL_NTS);

To determine if a data source supports procedures, an application calls SQLGetInfo with the SQL_PROCEDURES option.

For more information about procedures, see “Procedures” in Chapter 9, “Executing Statements.”