EXECUTE IMMEDIATE

Warning

SingleStore 9.0 gives you the opportunity to preview, evaluate, and provide feedback on new and upcoming features prior to their general availability. In the interim, SingleStore 8.9 is recommended for production workloads, which can later be upgraded to SingleStore 9.0.

Syntax

SQL

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:

SQL

DELIMITER //

CREATE OR REPLACE PROCEDURE test(var_name INT)
AS
BEGIN
    EXECUTE IMMEDIATE CONCAT('ECHO SELECT var_name');
END//

DELIMITER ;

SQL

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:

SQL

DELIMITER //

CREATE OR REPLACE PROCEDURE `test`(var_name INT)
AS
BEGIN
    EXECUTE IMMEDIATE CONCAT('ECHO SELECT ', var_name);
END//

DELIMITER ;

SQL

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.

SQL

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:

SQL

+------------+
| 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.

SQL

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:

SQL

+----------+
| 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.

SQL

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:

SQL

+------+----------+
| 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:

SQL

+------+-----------+
| 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.

SQL

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.

EXECUTE IMMEDIATE

On this page

Syntax

Arguments

Example 1: Feature tracking

Example 2: Table analysis

Example 3: Table mapping

Example 4: Initializing variables

Was this article helpful?

On this page

Was this article helpful?