Preparing SQL Statements

Prepared query is a way of querying for tools such as ADO, the ability to reuse execution plans, Prepared query is not a function of the TSQL language.

The SQL Server relational engine introduces full support for preparing SQL statements before they is executed. If an application have to execute a SQL statement several times, it can use the database API to do the following:

• Prepare the statement once. This compiles the SQL statement to an execution plan.

• Execute the precompiled execution plan every time it has to Execute the statement. This prevents has to recompile the SQL statement on each execution after the first time.

  The

  Preparing and executing statements are controlled by API functions and methods. It is a part of the Transact-SQL language. The Prepare/execute model of executing SQL statements is supported by the SQL Server Native Client OLE DB Provider and the SQL Server Native Client ODBC driver. On a prepare request, either the provider or the driver sends the statement-SQL Server with a request to prepare the St Atement. SQL Server compiles an execution plan and returns a handle for that plan to the provider or driver. On an execute request, either the provider or the driver sends the server a request to execute the plan, which is associated With the handle.

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

Excess use of the Prepare/execute model can degrade performance. If A statement is executed only once, a direct execution requires only one network round-trip to the server. Preparing and executing an SQL statement executed only one time

using(SqlCommand command =connection. CreateCommand ()) {Command.commandtext="SELECT * from users WHERE USERNAME = @username and @room"; Command. Parameters.addwithvalue ("@username", username); Command. Parameters.addwithvalue ("@room", guest); using(SqlDataReader DataReader =command. ExecuteReader ()) {// ...    }}

In database management systems, a prepared statement or parameterized statement are a feature used to exe Cute the same or similar database statements repeatedly with high efficiency. Typically used with SQL statements such as queries or updates, the prepared statement takes the form of a template into WH Ich certain constant values is substituted during each execution.

The typical workflow of using a prepared statement is as follows:

2. Prepare: The statement template is created by the application and sent to the database management system (DBMS). Certain values is left unspecified, called parameters, placeholders or bind variables (Labell Ed "?" below):
   • INSERT INTO PRODUCT (name, price) VALUES (?, ?)
4. The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without execut ing it.
6. Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS ex Ecutes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply ' Bread ' for the first parameter and ' 1.00 ' for the second parameter.

As compared to executing SQL statements directly, prepared statements offer both main advantages:^[1]

• the overhead of compiling and optimizing the statement is incurred only once, although the statement is executed multiple times. Not all optimization can being performed at the time the prepared statement are compiled, for both reasons:the best Plan D Epend on the specific values of the parameters, and the best plan for change as tables and indexes change over time.^[2]
• Prepared statements is resilient against SQL injection, because par Ameter values, which is transmitted later using a different protocol, need not being correctly escaped. If The original statement template is not a derived from external input, SQL injection cannot occur.

On the other hand, if a query was executed only once, server-side prepared statements can be slower because of the Addit Ional round-trip to the server. ^{[3] implementation limitations may also leads to performance Penalties:some Versions of MySQL did not cache results of prepared queries,[4] and some DBMSs such As PostgreSQL do not perform additional query optimization during Execution.[5][6"}

A stored procedure, which is also precompiled and stored on the server for later execution, have similar advantages. Unlike a stored procedure, a prepared statement is not normally written in a procedural language and cannot use or modify Variables or use control flow structures, relying instead on the declarative database query language. Due to their simplicity and client-side emulation, prepared statements is more portable across vendors.

Reference Documentation:

https://technet.microsoft.com/en-us/library/ms175528 (v=sql.105). aspx

Https://en.wikipedia.org/wiki/Prepared_statement

Preparing SQL Statements