Prepared statements

Another method for sending parameterized T-SQL statements to SQL Server is by using prepared statements. Leveraging prepared statements involves the following three different system procedures:

• sp_prepare: Defines the statement and parameters that are to be executed, creates an execution plan for the query, and sends a statement handle back to the caller, which can be used for subsequent executions
• sp_execute: Executes the statement defined by sp_prepare by sending the statement handle along with any parameters to SQL Server
• sp_unprepare: Discards the execution plan created by sp_prepare for the query specified by the statement handle

The first two procedures can optionally be combined into a single sp_prepexec statement to save a roundtrip to the server.

This method is not generally recommended for plan reuse as it is a legacy construct and may not take advantage of some of the benefits of parameterized statements that sp_executesql and stored procedures can leverage. It is worth mentioning, however, because it is used by some cross-platform database connectivity libraries, such as Open Database Connectivity (ODBC) or Java Database Connectivity (JDBC), as the default mechanism for sending queries to SQL Server.