SQL Reference - PREPARE

PREPARE

PREPARE is a SQL statement that allows you to create a prepared statement. A prepared statement is useful when you want to execute the same SQL query multiple times. The query is sent to the database server, parsed and compiled by the database engine. The prepared statement can be executed later as many times as needed, which can save significant processing time for complex queries.

Example

PREPARE stmt1 FROM 'SELECT * FROM table WHERE column = ?';
SET @var = 'value';
EXECUTE stmt1 USING @var;

Output

+--------+---------+
| column1 | column2 |
+--------+---------+
| row1    | row2    |
+--------+---------+

Explanation

In the example code, PREPARE statement is used to prepare the SQL statement ‘SELECT * FROM table WHERE column = ?’. The ? acts as a placeholder. Then, using the SET command, a value ‘value’ is assigned to @var. Finally, the EXECUTE statement is used to execute the prepared statement, substituting the placeholder with @var. The output shows the resultant table after executing the prepared SQL statement.

Example

PREPARE my_select (int) AS
SELECT name, age FROM employees WHERE id = $1;

EXECUTE my_select (123);

Output

    name   | age
-----------+-----
 John Doe  | 45

Explanation

The PREPARE statement is used to prepare a query for execution. The syntax is PREPARE name [ ( data_type [, ...] ) ] AS statement. The EXECUTE statement is then used to run the prepared statement. In the given example, the my_select statement is prepared to select the name and age of an employee with a specific ID, and then executed with the ID 123.

Example

DECLARE @SQLQuery AS NVARCHAR(500)
DECLARE @ParameterDefinition AS NVARCHAR(500)
DECLARE @ID AS INT
SET @ID = 5
SET @SQLQuery = N'SELECT * FROM Employees WHERE EmployeeID = @EmpID'
SET @ParameterDefinition = N'@EmpID INT'
EXEC sp_executesql @SQLQuery, @ParameterDefinition, @EmpID = @ID

Output

EmployeeID | EmployeeName | EmployeePosition
------------|--------------|-----------------
5           | John Doe     | Manager

Explanation

In the example, a SQL statement is created in which the EmployeeID is a parameter (@EmpID). The SQL statement is then executed with a specific value for this parameter (@ID = 5). This approach is using an SQL Server stored procedure sp_executesql which prepares, parses, compiles, and executes the SQL statement.

Example

DECLARE
  StudentName VARCHAR2(50);
  ClassID NUMBER(5);
  SQL_STMT   VARCHAR2(500);
BEGIN
  SQL_STMT := 'SELECT StudentName FROM Student WHERE ClassID = :id';
  PREPARE stmt FROM SQL_STMT;
  SET @id = 10;
  EXECUTE stmt USING @id;
  DEALLOCATE PREPARE stmt;
END;
/

Output

'John Doe'

Explanation

In the provided example, the PREPARE statement is used to prepare the SQL statement for execution. It substitutes a parameter :id within the SQL statement with a specified value 10 using the EXECUTE statement. As a result, the query selects the student’s name from the Student table where ClassID equals 10. The prepared statement is then deallocated. The output yields the name of the student as 'John Doe'.