Procedure Invocation Limitations

For maximum interoperability, procedures should be invoked using the ODBC {call procedure} extension to the SQL language. If you prepare a procedure without using the {call} syntax, use the EXECUTE in front of each stored procedure. This is because the SQL Server driver surrounds a statement it is preparing with other SQL statements. Thus, the statement being prepared is no longer the first in the batch.

With the SQL Server driver, there is no advantage to preparing a statement that invokes a procedure (instead of executing it directly). When the Generate Stored Procedures for Prepared Statements option is selected, the SQL Server driver prepares a statement by placing it in a procedure and compiling that procedure. Preparing a statement by placing it in a procedure and compiling it can be disabled, however. To ensure that stored procedures are never used to implement SQLPrepare, clear the Generate Stored Procedures check box in the SQL Server Setup dialog box, or set the SQL_USE_PROCEDURE_FOR_PREPARE option in SQLSetConnectOption to SQL_UP_OFF. This ensures that a prepared statement will be stored and executed at run time. Then stored procedures will not be used to implement SQLPrepare. In addition, all syntax error checking will be delayed until execution time.

When a SET NOCOUNT ON statement is executed, multiple statements embedded in a stored procedure do not create multiple results as they should. The driver ignores row counts that are generated by SQL statements inside of a stored procedure.