SQLPrepare
The SQL Native Client ODBC driver creates a temporary stored procedure from prepared SQL statements. Stored procedures are an efficient way to execute a statement multiple times, but creating stored procedure is more expensive than executing a simple statement. As a general rule, consider using SQLPrepare and SQLExecute if the application will submit an SQL statement more than three times.
Note
SQL Server 2005 supports the prepare/execute model of ODBC. This discussion of SQLPrepare/SQLExecute behavior is applicable only to versions of SQL Server earlier than 7.0.
A temporary stored procedure created by SQLPrepare is named **#odbc#**useridentifier, where useridentifier is up to six characters of the user-name concatenated with up to eight digits that identify the procedure.
SQLPrepare creates the temporary stored procedure if all parameter values have been bound or if the SQL statement does not contain parameters. SQLExecute creates the procedure if all parameters were not bound when SQLPrepare was called.
SQLPrepare can create stored procedures more efficiently than SQLExecute, and we recommend using SQLBindParameter to bind parameter variables before calling SQLPrepare.
If the CREATE PROCEDURE statement used to generate a temporary stored procedure returns an error, SQLPrepare or SQLExecute submits the statement to SQL Server with the SET NOEXEC or SET PARSEONLY option enabled (depending on the statement type). SQL Server checks the syntax of the statement and returns any errors.
SQLExecute can return any ODBC SQLSTATE and any SQL Server error that can be returned by SQLPrepare.
The SQL Native Client ODBC driver creates a new temporary stored procedure if the InputOutputType, ParameterType, ColumnSize, or DecimalDigits values are altered in calls to SQLBindParameter on a prepared statement. A new temporary stored procedure will not be created when bound parameters are pointed to new buffers in client memory, the length of client memory is changed, or the pointer to the length or indicator value for the parameter is altered.
If a connection cannot create a stored procedure for any reason (such as lack of permission), the SQL Native Client ODBC driver does not use a stored procedure but, instead, submits the SQL statement each time SQLExecute is called.
By default, the SQL Native Client ODBC driver drops temporary stored procedures when the connection is broken (SQLDisconnect is called for the connection). This may present problems if the connection is expected to remain open indefinitely. The default behavior can be changed using the driver-specific connection option SQL_USE_PROCEDURE_FOR_PREPARE.
Note
If SET NOCOUNT ON has been executed, multiple statements embedded in a stored procedure do not create multiple result sets as they should. Row counts generated by SQL statements inside a stored procedure are ignored by the driver.
See Also
Concepts
ODBC API Implementation Details