Preparing and Executing Statements

With Microsoft® SQL Server™ tools, such as SQL Query Analyzer or the osql utility, a user can key in and execute one or more Transact-SQL statements. Everything needed by the Transact-SQL statements must be specified in the batch of statements executed. Everything must be part of the character text of the Transact-SQL statements.

The database APIs support many options for executing a Transact-SQL statement. The most important options are:

• Using precompiled execution plans of frequently used statements. If a Transact-SQL statement is executed several times, the application can prepare the statement once, and then execute it as many times as needed. Preparing a statement directs the server to compile the statement into an execution plan. Subsequent executions are faster because they use the precompiled plan so the statement has to be compiled only once.

  For better performance, the statement preparation may be deferred until the statement is executed or a metaproperty operation (such as SQLDescribeCol or SQLDescribeParam in ODBC) is performed. This is the default behavior. Any errors in the statement being prepared are not known until the statement is executed or a metaproperty operation is performed. Setting appropriate statement options can turn off this default behavior.

• Binding program variables with the parameters. Instead of having to include the actual data values for input parameters as part of the Transact-SQL statement, an application can associate, or bind, program variables with the parameters. This means that the parameter values do not have to be converted to character strings to be included in the text of the Transact-SQL statement, but instead can be used in their native format.

Prepared statements cannot be used to create temporary objects in SQL Server 2000 or SQL Server 7.0. Prepared statements cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.