dbrpcinit

Initializes a stored procedure or a remote stored procedure.

Syntax

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

where

dbproc

Is the DBPROCESS structure that is the handle for a particular workstation/ 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 cancel 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 below.

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 below.

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 datatypes; 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 datatypes 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) participate in transactions normally and can be rolled back. Remote stored procedures executed on a remote SQL Server cannot be rolled back.

See Also

dbnextrow, dbresults, dbretdata, dbretstatus, dbrpcparam, dbrpcsend, dbsqlok