EXECUTE IMMEDIATE

Runs a SQL query.

You specify the query using a string, which you can build dynamically.

Syntax

EXECUTE IMMEDIATE SQL_string
[INTO variable [, ...]]

Arguments

SQL_string

A string expression containing one or more SQL statements. Multiple SQL statements are separated with a semicolon. The rightmost SQL statement may optionally have a semicolon.

variable

A list of variables to be specified with the SELECT … INTO <variable> statement.

Note

Procedural language variables are available to a stored procedure, but not queries run via an EXECUTE IMMEDIATE command contained within the procedure. If a procedural language variable is referenced by a query and not the procedure, an error is generated.

For example, the following stored procedure will generate an error when called:

DELIMITER //
CREATE OR REPLACE PROCEDURE test(var_name INT)
AS
BEGIN
EXECUTE IMMEDIATE CONCAT('ECHO SELECT var_name');
END//
DELIMITER ;
CALL test(1);
ERROR 1054 ER_BAD_FIELD_ERROR: Unhandled exception Type: ER_BAD_FIELD_ERROR (1054) Message: Unknown column 'var_name' in 'field list' 1 warning Callstack: #0 Line 3 in `test1`.`test`

The variable var_name should be referenced inside the stored procedure test and not inside the SQL statement:

DELIMITER //
CREATE OR REPLACE PROCEDURE `test`(var_name INT)
AS
BEGIN
EXECUTE IMMEDIATE CONCAT('ECHO SELECT ', var_name);
END//
DELIMITER ;
CALL test(1);
+---+
| 1 |
+---+
| 1 |
+---+

Example 1: Feature tracking

Using the parameter customer_id, the procedure below creates a feature table for the customer and populates the table with the default feature set. The INSERT statement string is built using QUOTE, which allows single quotes to be inserted around the feature_id field values. The ECHO SELECT statement writes the query results to the console.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
USE singlestore_docs_example;
DELIMITER //
CREATE PROCEDURE create_feature_table(customer_id VARCHAR(20)) AS
DECLARE
statement1 TEXT;
statement2 TEXT;
BEGIN
statement1 = CONCAT('CREATE TABLE ', customer_id, '_FEATURE(feature_id TEXT)');
statement2 = CONCAT('INSERT INTO ', customer_id, '_FEATURE VALUES (', QUOTE('F000'), '),(', QUOTE('F001'), '),(', QUOTE('F002'), ')');
EXECUTE IMMEDIATE CONCAT(statement1, ';', statement2);
END
//
DELIMITER ;
CALL create_feature_table('C1000');
SELECT * FROM C1000_FEATURE ORDER BY feature_id;

Output:

+------------+
| feature_id |
+------------+
| F000 |
| F001 |
| F002 |
+------------+

Example 2: Table analysis

The following procedure retrieves all of the column names for the table_name located in schema_name. Using EXECUTE IMMEDIATE, it returns the number of unique values in each column. It uses the information_schema database to get the column names. This database contains metadata about the other databases that exist in the SingleStore instance.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t(id INT, color TEXT);
INSERT INTO t VALUES (1, 'red'), (2, 'green'), (3, 'blue'), (4, 'blue'),
(5, 'red');
DELIMITER //
CREATE PROCEDURE analyze_table(_table_schema TEXT, _table_name TEXT) AS
DECLARE
/* Use static parameterized SQL when declaring 'q' because _table_name
and _table_schema are placeholders for constant values. */
qry QUERY(column_name VARCHAR(64)) = SELECT COLUMN_NAME FROM
information_schema.COLUMNS WHERE TABLE_NAME =_table_name AND
TABLE_SCHEMA = _table_schema;
arr ARRAY(RECORD(column_name VARCHAR(64)));
_column_name TEXT;
exec_string TEXT;
BEGIN
arr = COLLECT(qry);
/* When creating the 'exec_string's below, use dynamic SQL because _table_name
and _table_schema are not placeholders for constant values. */
exec_string = CONCAT('ECHO SELECT COUNT(*) FROM ', _table_schema, '.', _table_name);
EXECUTE IMMEDIATE exec_string;
FOR x IN arr LOOP
_column_name = x.column_name;
exec_string = CONCAT('ECHO SELECT DISTINCT(', _column_name, ') FROM ', _table_schema, '.', _table_name, ' ORDER BY ', _column_name);
EXECUTE IMMEDIATE exec_string;
END LOOP;
END
//
DELIMITER ;
CALL analyze_table('singlestore_docs_example','t');

Output:

+----------+
| COUNT(*) |
+----------+
| 5 |
+----------+
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
+------+
+-------+
| color |
+-------+
| blue |
| green |
| red |
+-------+

Example 3: Table mapping

Using EXECUTE IMMEDIATE, the following example demonstrates how to call functions dynamically.

DROP DATABASE IF EXISTS singlestore_docs_example;
CREATE DATABASE singlestore_docs_example;
use singlestore_docs_example;
CREATE TABLE t1(num INT);
CREATE TABLE t2(num INT);
INSERT INTO t1 VALUES (5),(9),(11);
INSERT INTO t2 VALUES (36),(64),(100);
DELIMITER //
CREATE FUNCTION sqr(num INT) RETURNS INT AS
BEGIN
RETURN pow(num,2);
END
//
DELIMITER //
CREATE PROCEDURE map(tbl TEXT, func TEXT) AS
DECLARE
str TEXT;
BEGIN
str = CONCAT('ECHO SELECT num, ', func , '(num) FROM ', tbl , ' ORDER BY num');
EXECUTE IMMEDIATE str;
END
//

Call the map function defined above, applying sqr to each row of table t1:

CALL map('t1','sqr');

Output:

+------+----------+
| num | sqr(num) |
+------+----------+
| 5 | 25 |
| 9 | 81 |
| 11 | 121 |
+------+----------+

Call the map function defined above, applying sqrt to each row of table t2:

CALL map('t2','sqrt');

Output:

+------+-----------+
| num | sqrt(num) |
+------+-----------+
| 36 | 6 |
| 64 | 8 |
| 100 | 10 |
+------+-----------+

Example 4: Initializing variables

The following example assigns values to variables using the EXECUTE IMMEDIATE statement.

DELIMITER //
CREATE OR REPLACE PROCEDURE into_var () AS
DECLARE
row_c INT; qry VARCHAR(50);
BEGIN
qry = "SELECT COUNT(*) FROM hrRec";
EXECUTE IMMEDIATE qry INTO row_c;
ECHO SELECT row_c AS "Row Count";
END //
DELIMITER ;
CALL into_var();
+-----------+
| Row Count |
+-----------+
|         5 |
+-----------+

Related Topics

  • ARRAY: A collection of data elements that share the same data type.

  • QUERY: A data type representing a SELECT statement.

  • QUOTE: Returns a string enclosed in single quotes.

Last modified: October 17, 2023

Was this article helpful?