SQLPrepare

SQL Server does not directly support the Prepare/Execute model of ODBC.

First, a temporary stored procedure is created from the statement, because stored procedures are an efficient way to execute a statement more than once. The procedure is named #odbc#useridentifier, where user is up to 6 characters of the username and identifier is up to 8 digits and identifies the statement. The procedure is created at prepare time if all parameters have been set, or at execute time if all parameters were not set at prepare time or if any parameter has been reset since the procedure was created. Because of this, SQLExecute can return any errors that SQLPrepare can return.

If CREATE PROCEDURE returns an error, SQLPrepare 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.

If a user cannot create a stored procedure for any reason (such as lack of permission), the SQL Server driver does not use a stored procedure but, instead, submits the SQL statement each time SQLExecute is called.

You can disable a generation of stored procedures for SQLPrepare. If disabled, the statement will be stored and re-executed at execution time. You can disable generation of stored procedures in either of two ways:

• Clear the Generate Stored Procedures for Prepared Statements check box in the ODBC SQL Server Driver Setup dialog box.
• Set the SQL_USE_PROCEDURE_FOR_PREPARE option of the SQLSetConnectOption function to SQL_UP_OFF.

When generation of stored procedures is disabled and the statement is stored and executed at run time, all syntax error checking is delayed until run time.

Note that if SET NOCOUNT ON has been executed, multiple statements embedded in a stored procedure do not create multiple results sets as they should. Row counts that are generated by SQL statements inside a stored procedure are ignored by the driver.