Tech tip

Regarding the dynamic select statements in stored procedures: If you need to fetch the data from table_name and column_name, should they be sent as parameters to the stored procedure? Do you have any samples of this functionality? Please provide the scripts.

To clarify, here are some definitions, examples and restrictions regarding the SQL request:

Dynamic SQL is defined as a technique for generating and executing SQL commands dynamically from a stored procedure at runtime. It is constructed as a concatenated character string and is passed to DBC.SysExecSQL for execution. Dynamic SQL may also be subject to run-time errors.

Static SQL is defined as a pre-constructed SQL compiled into the stored procedure. It may be parameterized, and it can still be optimized for each execution.

Here is an example of static SQL:

REPLACE PROCEDURE static-sql (IN sal DEC(9,2)
,IN emp_num INT)
BEGIN
UPDATE emp1
SET salary_amount = :sal
WHERE employee_number = :emp_num);
END;
CALL static_sql(50000, 1018);

Here is an example of dynamic SQL:

REPLACE PROCEDURE dyn_sql (IN col1 CHAR(15)
,IN val1 CHAR(10)
,IN emp_num CHAR (8))
BEGIN
CALL DBC.SysExecSQL('UPDATE emp1 SET '|| :col1 || '=' || :val1 || '
WHERE employee_number = ' || :emp_num);
END;
CALL dyn_sql('salary_amount','50000','1018');
/= Updates emplyee 1018 salary_amount to $50,000 =/
CALL dyn_sql('job_code','567890','1018');
/= Updates employee 1018 job_code to 567890 =/

Dynamic SQL does have some usage restrictions within stored procedures.
1. The creating user must also be the owner of the procedure in order to
have the right to use dynamic SQL.
2. The size of the SQL command string can’t exceed 32000.
3. Multi-statement requests are not supported.
4. The ending semicolon is optional on the SQL command.
The following SQL statements can’t be used as dynamic SQL in stored procedures:

CALL
CREATE PROCEDURE
DATABASE
EXPLAIN
HELP
REPLACE PROCEDURE
SHOW
SELECT
SELECT INTO
SET SESSION ACCOUNT
SET SESSION COLLATION
SET SESSION DATEFORM
SET TIME ZONE

What about the SELECT request? Is there a way to achieve that functionality?

As mentioned previously, SELECT and SELECT INTO statements can’t be used in dynamic SQL. However, an INSERT-SELECT statement can be used in dynamic SQL. By using a global temporary table, a “dynamic” select can be achieved as shown in the following example:

-- create temporary table to be used by procedure
create global temporary table dyn_sql_t (col varchar(30))
on commit preserve rows;

REPLACE PROCEDURE dyn_sql
(IN tbl VARCHAR(30), IN col1 VARCHAR(30),
IN col2 VARCHAR(30),IN selval VARCHAR(30),
OUT resultval VARCHAR(30))
BEGIN
DELETE dyn_sql_t ALL;
CALL DBC.SysExecSQL (
'INSERT INTO dyn_sql_t SELECT ' || :col1 ||
' FROM ' || :tbl ||
' WHERE ' || :col2 || ' = ''' || :selval || '''');
SELECT col INTO :resultval FROM dyn_sql_t;
END;

-- create a test table with some data

create table some_t (some_col1 varchar(30), some_col2 varchar(30))
unique primary index (some_col2);

insert into some_t ('some_val1a', 'some_val2a');
insert into some_t ('some_val1b', 'some_val2b');
insert into some_t ('some_val1c', 'some_val2c');

-- call procedure to select where table, column to select,
-- column for condition, and value for condition are parameters.

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2b', resultval);
-- resultval is returned as 'some_val1b'

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2a', resultval);
-- resultval is returned as 'some_val1a'

call dyn_sql ('some_t', 'some_col1', 'some_col2', 'some_val2c', resultval);
-- resultval is returned as 'some_val1c'

call dyn_sql ('some_t', 'some_col2', 'some_col1', 'some_val1a', resultval);
-- resultval is returned as 'some_val2a'

Note that this procedure makes some assumptions about the data type of the selected column and the condition column. Using VARCHAR provides some flexibility (with implicit conversions between data types). However, a more sophisticated procedure may need to get information from the data dictionary about data types to correctly handle the specific data types for the columns.

Note also that this process assumes the immediate owner of the procedure has SELECT privilege on any possible table that the procedure is expected to be able to select from. For instance, the caller may need to grant SELECT privilege to the immediate owner of the procedure prior to calling it, or the immediate owner of the procedure may need to be higher in the owner hierarchy than any possible table (and grant itself the SELECT privilege on the table within the procedure if it does not already have the explicit privilege).

Finally, this procedure is a simple example and does not address the issues of not finding a qualifying row or finding more than one qualifying row.