The SQLParamOptions function allows an application to specify an array of multiple values for the parameters that are assigned by SQLBindParameter. By calling SQLParamOptions with a crow greater than 1, the driver generates an SQL statement batch or a stored procedure batch to execute multiple SQL statements.
SQLParamOptions is usually used with DML. Any DDL statements that are used with SQLParamOptions will have the same result as executing the statement serially crow times. For statements that do not contain parameter markers, the statement will still be executed crow times.
A stored procedure batch is used for SQLPrepare/SQLExecute and for canonical procedure invocations that do not use any data-at-execution parameters. Stored procedure output parameters are returned.
An SQL batch (also called a language event) is used for SQL commands and for canonical procedure invocations that use data-at-execution parameters. Any stored procedure output parameters are not returned. If a SELECT statement is used, multiple results sets will be returned, and the application must use SQLMoreResults to process them. After SQLParamData, SQLExecDirect, or SQLExecute returns success, the value returned in pirow is initially set to 1, and SQLMoreResults increments this value as you process each results set.
SQLRowCount will return the total of all rows that are affected by all statements in the SQL or stored procedure batch that contains no SELECT statements. The value returned by SQLRowCount is undefined if one or more SELECT statements are included. Also, when the original SQL contains multiple statements, the value returned in pirow can be incorrect.
While processing a batch, SQL Server may return an error. In some cases, SQL Server cancels the current command only, and the remainder of the batch is processed. For these errors, one or more executions will fail, and SQLExecDirect, SQLExecute, or SQLParamData will return SQL_ERROR. The value returned in pirow will be set to crow because all parameter rows were processed. SQLError will return the errors that occurred, but there is no way to determine which parameter row(s) caused the error. Because all parameter rows were processed, rebinding and continuing execution is not necessary.
In other cases, an error causes SQL Server to cancel the entire batch. For these errors, the value returned in pirow will contain the affected parameter row. You must rebind and continue execution to process the remaining parameter rows.
When data truncation occurs for a parameter, processing continues for the remaining parameters. SQL_SUCCESS_WITH_INFO is returned, along with a warning message. However, when an error (such as a conversion error) occurs on the client for one of the parameters, the entire batch execution is stopped and no execution is done for any row.
While SQLParamData returns SQL_NEED_DATA, it updates the value of pirow as it processes data-at-exec parameters, and the final SQLParamData updates the value of pirow based on any errors that are encountered during execution. Therefore, if an error is encountered with a row during execution, the value in pirow may be larger during the data-at-exec parameter processing than it is after the execution has completed.
When connected to SQL Server 4.2x, SQLParamOptions is limited as follows:
Note When you pass a non-null value for pirow, the driver will continue to place the current row number in that memory address, even if SQLParamOptions is effectively "turned off" by passing a crow of 1. Therefore, if the pirow address supplied by the application becomes an invalid address (for example, a local variable-leaving scope) it is possible that the driver might fail.