Dynamic SQL

NuoDB SQL supports the use of SQL statements that are defined and compiled at runtime for use in SQL stored procedures or user defined functions (UDFs).

Syntax

EXECUTE IMMEDIATE sql_statement
    [ INTO target_var [ , target_var ] ... ]
    [ USING VALUES input_var [ , input_var ] ... ]

Description

Dynamic SQL and the EXECUTE IMMEDIATE statement allow any SQL statement to be executed within a SQL stored procedure , trigger or UDF.

Most procedural language statements are used in standalone statements without the need to create a stored procedure.

EXECUTE IMMEDIATE allows SQL statements to be defined and compiled at runtime, thereby allowing the SQL statement to be dynamically built at execution time by substituting parameter values or concatenating text strings. Concatenating text strings allows building SQL statements that select from different tables and columns. The EXECUTE IMMEDIATE statement supports the following capabilities:

  • Define a SQL statement and assign its output to a local variable inside a stored procedure, trigger or function. When assigning an EXECUTE IMMEDIATE statement to a local variable it must be enclosed in parentheses.

  • Display a message using the THROW command.

  • Define the RETURNS result set in a stored procedure or a RETURN value from a function

  • Execute DML or DDL statements or any SQL statement (for example, UPDATE ROLLBACK, COMMIT), within a stored procedure or function.

  • Execute a stored procedure*, trigger, or function* using the INTO parameter to dynamically assign local, IN and/or INOUT parameter values of that procedural code block at runtime. Invoke the USING parameter to assign input values to the OUT and/or INOUT parameters of that called stored procedure.

When using a SELECT query as the sql-statement, the query will not be executed unless the result is assigned to a local variable or returned as a result set.

When writing dynamic SQL, NULL values need to be considered. This is true of SQL statements in general, not just dynamic SQL. For example, an SQL statement such as WHERE column = value will never be equal if value is NULL.

SQL statements that contain quoted literals also need to be considered when constructing dynamic SQL. NuoDB supports a function that will automatically escape quotes within a value. See QUOTENAME.

Parameters

sql_statement

A string representing a valid SQL statement to be used by EXECUTE IMMEDIATE. This can be any DDL or DML statement.

INTO target_var [ , target_var ]…​

The INTO parameter takes a comma separated list of one or more locally defined variables to be assigned the values returned from the SQL statement defined by sql_statement. It is assumed that sql_statement represents a call to a stored procedure or a scalar UDF. NuoDB supports assigning variables that are OUT and/or INOUT parameter values of the stored procedure or the scalar return value from the UDF. The INTO parameter is optional and only required if sql_statement returns one or more parameter values. DML statements would not require the INTO clause. The number of INTO variables defined must match the number of OUT and/or INOUT parameters to the stored procedure or one in the case of calling a scalar UDF. If no rows are returned by the SQL statement, the INTO variables are assigned NULL values.

USING VALUE input_var [ , input_var ]…​

One or more locally defined variables or input parameter values, in the form of a comma separated list of values used to substitute parameter values defined by sql_statement. This is optional and only required if the sql_statement contains parameters, designated by “?”. The number of USING values defined must match the number of parameters defined by sql_statement.

Examples

Example 1: Using EXECUTE IMMEDIATE for local variable assignment
DROP PROCEDURE IF EXISTS proc_exec1;
SET DELIMITER @
CREATE PROCEDURE proc_exec1 (IN inTableName STRING, IN inSelectCol STRING, inWhereCol STRING, IN inColValue STRING) AS
    VAR outParm STRING;
    outParm=(EXECUTE IMMEDIATE 'SELECT '||inSelectCol||' FROM '||inTableName ||' WHERE '||inWhereCol||' = '||inColValue);
    THROW inSelectCol||' = '||outParm;
END_PROCEDURE
@
SET DELIMITER ;

EXECUTE proc_exec1('HOCKEY','NAME','ID','1');
Procedure HOCKEY.PROC_EXEC1, NAME = PATRICE BERGERON
Example 2: Using EXECUTE IMMEDIATE to execute DDL
DROP PROCEDURE IF EXISTS proc_exec2;
SET DELIMITER @
CREATE PROCEDURE proc_exec2(IN inTableName STRING, IN inAltName STRING)
 as
    VAR sqlStr STRING='';
    VAR l_sqlStr STRING, l_field STRING, l_datatype STRING, l_length STRING;
    EXECUTE IMMEDIATE 'DROP TABLE IF EXISTS '||inAltName;
    sqlStr='CREATE TABLE '||inAltName||'(';
    FOR SELECT field,d.name FROM system.fields,system.datatypes d WHERE datatype = d.id AND tablename =  inTableName;
       l_field=field;
       l_datatype = name;
       l_sqlStr=sqlStr||l_field||' '||l_datatype||',';
       sqlStr=l_sqlStr;
    END_FOR;
    sqlStr=SUBSTR(l_sqlStr,1,LENGTH(l_sqlStr)-1)||');';
    EXECUTE IMMEDIATE sqlStr;
    EXECUTE IMMEDIATE 'Insert into '||inAltName||' SELECT * FROM '||inTableName||' limit 2;';
END_PROCEDURE;
@
SET DELIMITER ;

CALL proc_exec2('HOCKEY','AltHockey');

SELECT * FROM AltHockey;

 ID  NUMBER        NAME       POSITION   TEAM
 --- ------- ---------------- --------- ------

  1    37    PATRICE BERGERON  Forward  Bruins
  2    48    CHRIS BOURQUE     Forward  Bruins

SHOW TABLE AltHockey;

    Tables named ALTHOCKEY

    Found table ALTHOCKEY in schema HOCKEY

        Fields:
            ID bigint
            NUMBER integer
            NAME string
            POSITION string
            TEAM string
Example 3: Using EXECUTE IMMEDIATE to assign OUT/INOUT variables from a called stored procedure
DROP PROCEDURE IF EXISTS proc_calling;
DROP PROCEDURE IF EXISTS proc_get_data;

SET DELIMITER @

CREATE PROCEDURE proc_get_data(IN iNumber INTEGER, OUT ioNewPosition STRING)
AS
    ioNewPosition=(SELECT CASE position
           WHEN 'Goalie' THEN 'Fan'
           WHEN 'Fan' THEN 'Goalie'
           ELSE ioNewPosition END FROM hockey WHERE id = iNumber);
END_PROCEDURE;
@

CREATE PROCEDURE proc_calling (IN inNumber INTEGER)
AS
    VAR l_new_position STRING;
    EXECUTE IMMEDIATE 'CALL proc_get_data(?,?)' INTO l_new_position  USING VALUES  inNumber, NULL;
    UPDATE hockey SET position = l_new_position WHERE id = inNumber;
END_PROCEDURE;
@

SET DELIMITER ;

START TRANSACTION;
SET AUTOCOMMIT OFF;
CALL  proc_calling(24);

SELECT * FROM hockey WHERE id = 24;
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT  Goalie   Bruins

ROLLBACK;
SELECT * FROM hockey WHERE id = 24;
 ID  NUMBER     NAME    POSITION   TEAM
 --- ------- ---------- --------- ------
 24     1    MAX SUMMIT    Fan    Bruins
Example 4: Using EXECUTE IMMEDIATE to RETURN a value from a function.
DROP FUNCTION IF EXISTS func_player_info;
SET DELIMITER @
CREATE FUNCTION func_player_info( inTable STRING, inTeam STRING)
RETURNS table tmp_hockey(Number BIGINT, Name STRING, Team STRING)
 AS
RETURN (EXECUTE IMMEDIATE 'SELECT number,Name,Team
FROM '||inTable||' WHERE team = ?' USING VALUES inTeam);
END_FUNCTION;
@
SET DELIMITER ;

SELECT * FROM func_player_info('HOCKEY','Bruins') LIMIT 2;

 NUMBER        NAME        TEAM
 ------- ---------------- ------

    1    MAX SUMMIT       Bruins
   11    GREGORY CAMPBELL Bruins
Example 5: Any procedural can also run outside of a trigger, a stored procedure, or a UDF.
if (select 1 from dual) update hockey set position = 'Goalie' where id = 20; end_if;