PREPARE and EXECUTE in SQL

In testing we often want to try a simple bit of code to check a result or simply to find out how something works. One way to do this is to PREPARE a statement feed it variables and then EXECUTE it. This is a straight-forward process in SQL. The syntax looks like this:

PREPARE statement_name FROM 'syntax of statement with vars as ?s';
SET @a = input;
SET @b = more-input;
EXECUTE statement_name USING @a, @b;

The question marks in the PREPARE statement are variables which are SET before the statement is EXECUTEd.

Here is a simple example:

PREPARE addition_stmnt FROM 'SELECT (? + ?) AS sum';
SET @a = 4;
SET @b = 5;
EXECUTE addition_stmnt USING @a, @b;

The result will be a simple table with a column called "sum" with the result of "9".

To DEALLOCATE or DROP a PREPARED statement the following syntax is used:

DEALLOCATE PREPARE statement_name

< ORDER BY SQL | PRIMARY KEY Constraint >


Send notes in disappearing ink!

Interesting Pages