SqlRpcInit%

Initializes a stored procedure or a remote stored procedure.

Syntax

SqlRpcInit% ( sqlconn%, rpcname$, options% )

where

sqlconn%

Is a SQL Server connection. The value of sqlconn% is returned by SqlOpen%.

rpcname$

Is 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
SQLRPCRECOMPILE	Recompiles a stored procedure before it is executed.
SQLRPCRESET	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 SqlRpcInit%) 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 SqlRpcInit% once to initialize a new stored procedure.
2. Call SqlRpcParam% 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 SqlRpcSend% or SqlRpcExec% to send the entire stored procedure batch to SQL Server.
5. Call SqlOk% to wait for SQL Server to start returning results.
6. Call SqlResults% to process the results from each stored procedure.

   If SqlResults% returns SUCCEED, call SqlNextRow% until it returns NOMOREROWS to process the normal results from the stored procedure.

   If SqlResults% returns NOMORERPCRESULTS, 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 SqlResults% returns NOMORERESULTS.
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 SqlResults% returns NOMORERPCRESULTS (for all stored procedures in a batch except the last one) or NOMORERESULTS (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 SqlRetStatus& and SqlHasRetStat% to retrieve the return status number.
• Call SqlNumRets% to determine the number of return parameters.
• Call SqlRetData$, SqlRetType%, SqlRetLen&, and SqlRetName$ 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 causes DB-Library for Visual Basic to pass parameters in their native datatypes; using an EXECUTE statement passes them as ASCII characters. Calling stored procedures with DB-Library functions works faster and usually more efficiently than an EXECUTE statement because the server is not 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 SqlRetData$. (Note, however, that a return parameter must be specified as such when it is first added to the stored procedure through SqlRpcParam%.)

  When 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 sqlconn% 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

SqlNextRow%, SqlResults%, SqlRetData$, SqlRetStatus&, SqlRpcParam%, SqlRpcSend%, SqlOk%