Statements can be prepared before they are executed, or they can be executed directly. The Prepared property of the Command object allows you to specify whether to prepare a statement.
If the Prepared property is set to TRUE, a statement string is parsed and optimized at the first execution. Any subsequent execution of the statement uses the “compiled” version. It takes a longer time for the first execution, but performance improves for subsequent executions because the statement has already been parsed and optimized beforehand. However, if you are executing the statement string only one time, you should execute the statement directly instead of preparing it.
The Prepared property can be used when executing a statement with multiple parameter sets. An application can execute a parameterized statement more than once by supplying a different parameter set at each execution instead of reconstructing the statement whenever the parameter set is different.
Microsoft® SQL Server™ version 7.0 now directly supports the prepare/execute model of OLE DB and ODBC.
When a statement is prepared in an earlier version of SQL Server, SQLOLEDB creates a temporary stored procedure for the statement. This temporary stored procedure exists in tempdb and is not dropped until the Recordset or Connection object is closed.
When using MSDASQL, this option can be disabled through the SQL Server ODBC Data Source Setup dialog box if an ODBC data source is used to connect to SQL Server. If the option is disabled, the SQL statement is stored and then sent to the server each time it is executed.
This example shows using a prepared statement to update a query and dynamically construct the query with a different set of parameters at execution time.
Dim cn As New ADODB.Connection
Dim cmdPrep1 As New ADODB.Command
Dim prm1 As New ADODB.Parameter
Dim prm2 As New ADODB.Parameter
Dim strCn As String
strCn = "Server=MyServerName;Database=pubs;Trusted_Connection=yes"
cn.Provider = "sqloledb"
cn.Open strCn
Set cmdPrep1.ActiveConnection = cn
cmdPrep1.CommandText = "UPDATE titles SET type=? WHERE title_id=?"
cmdPrep1.CommandType = adCmdText
cmdPrep1.Prepared = True
Set prm1 = cmdPrep1.CreateParameter("Type", adChar, adParamInput, 12, "New Bus")
cmdPrep1.Parameters.Append prm1
Set prm2 = cmdPrep1.CreateParameter("Title_id", adChar, adParamInput, 6, "BU7832")
cmdPrep1.Parameters.Append prm2
cmdPrep1.Execute
cmdPrep1("Type") = "New Cook"
cmdPrep1("title_id") = "TC7777"
cmdPrep1.Execute
cn.Close
This example updates data in the titles table by using different parameter values. The query string is prepared so that different sets of parameters can be supplied. Two parameters are required for the update operation: type and title_id. They are created by the two CreateParameter methods and appended to the Parameters collection with the Append method.
The first set of parameters has the values New Bus and BU7832. Because the Prepared property is set to True, different values can be supplied to cmdPrep1 without reconstructing and reexecuting the query string