Dynamic SQL

Dynamic queries give you the most flexibility in defining SQL statements.

Before proceeding to the rest of this section, see Static Parameterized SQL to understand how static parameterized queries work.

Using PSQL variables, you can substitute strings anywhere at run time. In the following example, the query SELECT id, name FROM customer ORDER BY <column name> is built dynamically using a string. The EXECUTE IMMEDIATE statement uses the string to run the query.

SET sql_mode = 'PIPES_AS_CONCAT';

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE product(id INT, description TEXT);

INSERT INTO product VALUES (1, 'white paper'), (2, 'blue pen'),
    (3, 'black pen');

DELIMITER //
CREATE PROCEDURE get_product_data(col TEXT) AS
DECLARE
  str TEXT;
BEGIN
  str = 'ECHO SELECT id, description FROM product ORDER BY ' || col;
  EXECUTE IMMEDIATE str;
END
//
DELIMITER ;

CALL get_product_data('description');

Output:

+------+-------------+
| id   | description |
+------+-------------+
|    3 | black pen   |
|    2 | blue pen    |
|    1 | white paper |
+------+-------------+

More examples of dynamic queries are shown below. These queries cannot be written as static parameterized queries because column names and table names are not constant values.

SELECT <column name> FROM location`

INSERT INTO <table name> (id) VALUES (1),(2),(3)

With dynamic queries, you’re not limited to using PSQL variables that represent column and table names. For instance, you can substitute a PSQL variable for a WHERE clause:

SELECT id, name FROM part <WHERE clause>

You can also substitute a PSQL variable for a SELECT statement:

SELECT <rest of SELECT statement>