Passing stored procedure arguments to FOR loop cursor

Question

I was wondering if it was possible to pass in string arguments from a stored procedure into a FOR loop cursor SELECT statement definition? i.e something like

CREATE PROCEDURE db_name.procedure_name (
    IN col_name VARCHAR(32), 
    IN tbl_name VARCHAR(32)
    ) 
BEGIN 
    ...

    FOR 
        RecordPointer AS c_sptable CURSOR FOR 
        SELECT DISTINCT col_name AS c_c1
        FROM tbl_name

    DO 
        ...

This essentially boils down to asking whether the cursor_specification syntax can take in parameters.

I would have thought this is possible as the Teradata documentation example here passes an integer into the SELECT statement. However, none of the following attempts worked in the example given below

1. Passing the string arguments to the SELECT statement directly (as above)
2. Using :col_name and :tbl_name instead of col_name and tbl_name
3. Constructing a statement containing the SELECT DISTINCT ... cursor definition before the FOR clause and then using EXECUTE IMMEDIATE directly after the CURSOR FOR clause.

This makes me believe I may need to use a DECLARE CURSOR clause instead but I was unable to do this either.

Extended

I am trying to create a Teradata stored procedure that takes as arguments an arbitrary table name, tbl_name, and a corresponding column name, col_name, as strings and dynamically builds a set of flag columns from the unique values in col_name. However, I am getting stuck when trying to pass these arguments into a FOR loop cursor.

Given the following example data

-- volatile table
CREATE MULTISET VOLATILE TABLE PRACTICE_DATA 
(
    EMPLOYEE_NUMBER INTEGER,
    FIRSTNAME VARCHAR(16),
    LASTNAME VARCHAR(16),
    EMPLOYEE_AGE INTEGER,
    EMPLOYMENT_LENGTH_YEARS INTEGER
)
UNIQUE PRIMARY INDEX (EMPLOYEE_NUMBER)
ON COMMIT PRESERVE ROWS;

INSERT INTO PRACTICE_DATA VALUES (101, 'Mike',      'James',    32, 1);
INSERT INTO PRACTICE_DATA VALUES (104, 'Tom',       'Jones',    24, 1);
INSERT INTO PRACTICE_DATA VALUES (107, 'Stella',    'Smith',    41, 7);
INSERT INTO PRACTICE_DATA VALUES (108, 'Gary',      'Bourke',   43, 4);
INSERT INTO PRACTICE_DATA VALUES (115, 'Jenny',     'Bourke',   50, 19);
INSERT INTO PRACTICE_DATA VALUES (119, 'Ben',       'Thompson', 28, 1);
INSERT INTO PRACTICE_DATA VALUES (121, 'Alexandra', 'Lu',       31, 4);
INSERT INTO PRACTICE_DATA VALUES (126, 'Daniel',    'Chen',     55, 6);

The following procedure gets the unique elements in the column EMPLOYMENT_LENGTH_YEARS and creates a set of flag variables, one for each unique value, where the flags are 1 if the individual has been working for that amount of time and 0 otherwise

CREATE PROCEDURE db_name.dynamic_flags_procedure (
    IN col_name VARCHAR(32), 
    IN tbl_name VARCHAR(32)
    ) 
BEGIN 
    DECLARE hc1 VARCHAR(32);
    DECLARE sql_stmt VARCHAR(2048);
    
    -- start sql stmt to create table and select the column 'FIRSTNAME'
    SET sql_stmt = 'CREATE MULTISET VOLATILE TABLE FLAG_TABLE AS (
                    SELECT 
                        FIRSTNAME';

    FOR 
        -- get the unique elements in col_name to loop over
        RecordPointer AS c_sptable CURSOR FOR 
        SELECT DISTINCT EMPLOYMENT_LENGTH_YEARS AS c_c1 -- here is the first issue, we have to specify EMPLOYMENT_LENGTH_YEARS rather than col_name or :col_name
        FROM PRACTICE_DATA                              -- here is the second issue, we have to specify PRACTICE_DATA rather than tbl_name or :tbl_name

    DO 
        -- create pointer to current unique element
        SET hc1 = RecordPointer.c_c1; 
        -- create flag column and add string to sql_stmt
        SET sql_stmt = sql_stmt || ', CASE WHEN ' || col_name || ' = ' || hc1 || ' THEN 1 ELSE 0 END AS "' || hc1 || '_flag"';

    END FOR; 

    -- add final part to the sql_stmt
    SET sql_stmt = sql_stmt || ' FROM ' || tbl_name || ') WITH DATA ON COMMIT PRESERVE ROWS;';
    EXECUTE IMMEDIATE sql_stmt;
END;

CALL db_name.dynamic_flags_procedure('EMPLOYMENT_LENGTH_YEARS', 'PRACTICE_DATA');
SELECT * FROM FLAG_TABLE;

The problem with this is that to make this work I had to hardcode the table name PRACTICE_DATA and column name EMPLOYMENT_LENGTH_YEARS directly into the FOR loop cursor

    FOR 
        -- get the unique elements in col_name to loop over
        RecordPointer AS c_sptable CURSOR FOR 
        SELECT DISTINCT EMPLOYMENT_LENGTH_YEARS AS c_c1
        FROM PRACTICE_DATA

whereas ideally we would like to replace those with tbl_name and col_name so that the procedure works on arbitrary tables and columns.

Answer 1

Passing values is one thing, but passing names requires dynamic SQL. In other words, you need to use the input values to build a string containing the actual SQL SELECT text (similar to what you did for EXECUTE IMMEDIATE in your example) and use a dynamic cursor to obtain the results. But FOR statement only accepts a static cursor, so you will also need to explicitly code the loop - something like this:

DECLARE cursor_txt VARCHAR(2048);
...
SET cursor_txt = ... /* your dynamic SELECT statement */;

BEGIN 
/* This block is similar to what FOR would do, but with dynamic cursor */
  DECLARE loop_done BYTEINT DEFAULT 0;
  DECLARE tblname VARCHAR(128);
  DECLARE colname VARCHAR(128);

  DECLARE result_row CURSOR FOR cursor_stmt; 
  /* Need to catch "no more rows" condition, not abort */
  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET loop_done=1; 

  PREPARE cursor_stmt FROM cursor_txt;

  OPEN result_row;
  FETCH result_row INTO tblname, colname;
  WHILE loop_done=0 DO
    ... /* body of loop here */
    FETCH result_row INTO tblname, colname;
  END WHILE;
  CLOSE result_row;
END;