MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server

Calling a Stored Procedure (OLE DB)

When executing a Microsoft® SQL Server™ stored procedure by using SQLOLEDB, the SQL Server driver supports both the ODBC CALL escape sequence and the Transact-SQL EXECUTE statement for executing stored procedures. SQLOLEDB returns output parameters and return codes regardless of the method you use to call a stored procedure.

The ODBC CALL escape sequence is the preferred method for calling a stored procedure. SQLOLEDB uses the remote procedure call (RPC) mechanism of SQL Server to optimize command processing. This RPC protocol increases performance by eliminating much of the parameter processing and statement parsing done on the server.

For example, the ODBC SQL statement

{call SalesByCategory('Produce', '1995')}

is preferred command text over the Transact-SQL form

EXECUTE SalesByCategory 'Produce', '1995'

A procedure can have zero or more parameters. It can also return a value. The general syntax for calling a procedure by using the ODBC CALL escape sequence is:

{[?=]call procedure_name[([parameter][,[parameter]]...)]}

In OLE DB, parameters to a stored procedure can be passed in the following ways:

• Hard-coding the data value.
  
  
• Using a parameter marker (?) to specify parameters, binding a program variable to the parameter marker, and then placing the data value in the program variable.

To support parameters, the ICommandWithParameters interface is exposed on the command object. To use parameters, the consumer first describes the parameters to the provider by calling the ICommandWithParameters::SetParameterInfo method (or optionally prepares a calling statement that calls the ICommandWithParameters::GetParameterInfo method). The consumer then creates an accessor that specifies the structure of a buffer and places parameter values in this buffer. Finally, it passes the handle of the accessor and a pointer to the buffer to ICommand::Execute. On later calls to ICommand::Execute, the consumer places new parameter values in the buffer and calls Execute with the accessor handle and buffer pointer.

A command that calls a temporary stored procedure using parameters must first call ICommandWithParameters::SetParameterInfo to define the parameter information, before the command can be successfully prepared. This is because the internal name for a temporary stored procedure differs from the external name used by a client and SQLOLEDB cannot query the system tables to determine the parameter information for a temporary stored procedure.

The steps in the parameter binding process are as follows:

1. Fill in the parameter information in an array of DBPARAMBINDINFO structures—that is, parameter name, provider-specific name for the data type of the parameter, or a standard data type name, and so on. Each structure in the array describes one parameter. This array is then passed to the ICommandWithParameters::SetParameterInfo method.
   
   
2. Call the ICommandWithParameters::SetParameterInfo method to describe parameters to the provider. SetParameterInfo specifies the native data type of each parameter. Following are SetParameterInfo arguments:
   • The number of parameters for which to set type information.
     
     
   • An array of parameter ordinals for which to set type information.
     
     
   • An array of DBPARAMBINDINFO structures.
3. Create a parameter accessor by using the IAccessor::CreateAccessor command. The accessor specifies the structure of a buffer and places parameter values in the buffer. The CreateAccessor command creates an accessor from a set of bindings. These bindings are described by the consumer by using an array of DBBINDING structures. Each binding associates a single parameter to the buffer of the consumer and contains information such as the following:
   • The ordinal of the parameter to which the binding applies.
     
     
   • What is bound (the data value, its length, and its status).
     
     
   • The offset in the buffer to each of these parts.
     
     
   • The length and type of the data value as it exists in the buffer of the consumer.

   An accessor is identified by its handle, which is of type HACCESSOR. This handle is returned by the CreateAccessor method. Whenever the consumer finishes using an accessor, the consumer must call the IAccessor::ReleaseAccessor method to release the memory it holds.

   When the consumer calls a method, such as ICommand::Execute, it passes the handle to an accessor and a pointer to a buffer itself. The provider uses this accessor to determine how to transfer the data contained in the buffer.

4. Fill in the DBPARAMS structure. The consumer variables from which input parameter values are taken and to which output parameter values are written are passed at run time to ICommand::Execute in the DBPARAMS structure. The DBPARAMS structure includes the following elements:
   • A pointer to the buffer from which the provider retrieves input parameter data and to which the provider returns output parameter data, according to the bindings specified by the accessor handle.
     
     
   • The number of sets of parameters in the buffer.
     
     
   • The accessor handle created in step 3.
5. Execute the command by using ICommand::Execute.