dbrpcinit

Initializes a stored procedure or a remote stored procedure.

Syntax

RETCODE dbrpcinit (
PDBPROCESS dbproc,
LPCSTR rpcname,
DBSMALLINT options );

Arguments

dbproc

Is the DBPROCESS structure that is the handle for a particular workstation/ Microsoft® SQL Server™ process. It contains all the information that DB-Library uses to manage communications and data between the workstation and SQL Server.

rpcname

Is a pointer to the name of the stored procedure to be invoked.

options

Is a 2-byte bitmask of options for stored procedures. Specify 0 to indicate no options. The following options are available.

 

Option	Description
DBRPCRECOMPILE	Recompiles a stored procedure before it is executed.
DBRPCRESET	Cancels a single stored procedure or a batch of stored procedures. If rpcname is specified, that new stored procedure is initialized after the cancellation is complete.

Returns

SUCCEED or FAIL.

Remarks

An application can execute a single stored procedure, or it can execute a batch containing multiple stored procedures. To execute a single stored procedure, you can use DB-Library functions (such as dbrpcinit) or the Transact-SQL EXECUTE statement. To retrieve the status number and parameter values returned by each stored procedure in a batch, you must use DB-Library functions.

To execute a single stored procedure or a batch of stored procedures using DB-Library functions

1. Call dbrpcinit once to initialize a new stored procedure.
2. Call dbrpcparam for each parameter of the stored procedure that does not have a default value.
3. Repeat steps 1 and 2 for each stored procedure in the batch.
4. Call dbrpcsend or dbrpcexec to send the entire stored procedure batch to SQL Server.
5. Call dbsqlok to wait for SQL Server to start returning results.
6. Call dbresults to process the results from each stored procedure.

   If dbresults returns SUCCEED, call dbnextrow until it returns NO_MORE_ROWS to process the normal results from the stored procedure.

   If dbresults returns NO_MORE_RPC_RESULTS, and you want to retrieve status number and return-parameter information returned by the stored procedure, follow the steps given later.

7. Repeat Step 6 until dbresults returns NO_MORE_RESULTS.
8. If you want to retrieve status number and return-parameter information returned by the last stored procedure in the batch, follow the steps given later.

After dbresults returns NO_MORE_RPC_RESULTS (for all stored procedures in a batch except the last one) or NO_MORE_RESULTS (for a single stored procedure, or for the last stored procedure in a batch), you can retrieve status number and return-parameter information for a stored procedure.

To retrieve status number and return-parameter information returned by a stored procedure using DB-Library functions

• Call dbretstatus and dbhasretstat to retrieve the return status number.
• Call dbnumrets to determine the number of return parameters.
• Call dbretdata, dbrettype, dbretlen, and dbretname about retrieve information for each return parameter.

Executing stored procedures with DB-Library functions has some advantages over using an EXECUTE statement:

• Using DB-Library functions to call a stored procedure passes parameters in their native data types; using an EXECUTE statement passes parameters as ASCII characters. Calling stored procedures with DB-Library functions works faster and usually more efficiently than an EXECUTE statement, because neither the application nor the server is required to convert native data types into their ASCII equivalents.
• Using DB-Library functions instead of an EXECUTE statement accommodates return parameters for stored procedures more quickly. With a remote stored procedure, the return parameters are always available to the application by calling dbretdata. (Note, however, that a return parameter must be specified as such when it is first added to the stored procedure through dbrpcparam.)

  If a stored procedure is called with an EXECUTE statement, the return parameter values are available only if the command batch containing the EXECUTE statement uses local variables, rather than constants, as the return parameters. This involves additional parsing each time the command batch is executed.

• The client application can use DB-Library functions to issue a stored procedure call directly to an Open Data Services server application. The Open Data Services server application will detect this request as a remote stored procedure event. The Open Data Services server application is not required to parse the language buffer to find out what the client is requesting.

Stored procedures executed on the local SQL Server (using the dbproc connection) generally participate in transactions and can be rolled back. Remote stored procedures executed on a remote SQL Server cannot be rolled back.

In SQL Server version 6.5 or later, you can pass stored procedure parameters when you open a cursor by calling the dbrpcinit function and the dbrpcparam function.

The dbrpcinit function has an option parameter: DBRPCCURSOR. Use DBRPCCURSOR to specify the initialization of an input-parameter list for a subsequent cursor-open operation on a stored procedure. When you use DBRPCCURSOR, you must set the rpcname parameter to NULL.

No pending remote procedure calls (RPCs) can exist when you call dbrpcinit with an option of DBRPCCURSOR. The dbrpcinit function returns FAIL if pending RPCs have been initiated.

You can call the dbrpcparam function to set the value for each stored procedure parameter that was used in the process of opening a cursor. Stored procedure parameters are no longer required to be constants, and they can be passed as follows.

To open a cursor on a stored procedure that passes program variables as parameters

1. Call the dbrpcinit function once to initialize a new stored procedure. Set the rpcname parameter to NULL, and set the option parameter to DBRPCCURSOR.
2. Call the dbrpcparam function once per parameter to set the value of each parameter of the stored procedure.
3. Call the dbcursoropen function to open the cursor, and in the stmt parameter, specify only the stored procedure name.
4. Call the remaining cursor functions as you ordinarily would.

See Also