Note Microsoft® SQL Server™ version 7.0 supports the prepare/execute model of ODBC. The following discussion of SQLPrepare/SQLExecute behavior is applicable to earlier versions of SQL Server only.
The SQL Server ODBC driver creates a temporary stored procedure from prepared SQL statements. Stored procedures are an efficient way to execute a statement multiple times but stored procedure creation is more expensive than simple statement execution. As a general rule, consider using SQLPrepare and SQLExecute if the application will submit an SQL statement more than three times.
A temporary stored procedure created by SQLPrepare is named #odbc#useridentifier, where useridentifier is up to 6 characters of the user-name concatenated with up to 8 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 it is suggested that SQLBindParameter be used to bind parameter variables prior to 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 Server 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 Server ODBC driver does not use a stored procedure but, instead, submits the SQL statement each time SQLExecute is called.
By default, the SQL Server 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.
| SQLBindParameter | SQLSetConnectAttr |