Calling a Stored Procedure

The Microsoft® SQL Server™ ODBC driver supports both the ODBC CALL escape sequence and the Transact-SQL EXECUTE statement for executing stored procedures; the ODBC CALL escape sequence is the preferred method. Using ODBC syntax enables an application to retrieve the return codes of stored procedures and the SQL Server ODBC driver is also optimized to use a protocol originally developed for sending remote stored procedure (RPC) calls between SQL Servers. This RPC protocol increases performance by eliminating much of the parameter processing and statement parsing done on the server.

The ODBC CALL 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.

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 a parameter is an input or an input/output parameter, it can be a literal or a parameter marker. If the parameter is an output parameter, it must be a parameter marker because the output is unknown. 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, the driver instructs the data source to use the default value for the first parameter. For example:

{call procedure_name()}

If the procedure does not have any parameters, the procedure can fail. If a procedure is called without parentheses, the driver does not send any parameter values. For example:

{call procedure_name}

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. (Parameters are numbered ordinally, beginning with a value of 1.)

{call InsertOrder(, 10, ?, ?, ?)}

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. Other ways to specify the default value of an input or input/output parameter are to set the value of the length/indicator buffer bound to the parameter to SQL_DEFAULT_PARAM, or to use the DEFAULT keyword.

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.

To call remote procedures

See Also