I'm trying to create a dunamic procedure that, given a specific dbname and tablename, creates a set of select statements using all column names available for that table.
I'm using the following code:
REPLACE PROCEDURE sum_col ( IN TABLENAME VARCHAR(50), DBNAME VARCHAR(286) ) BEGIN DECLARE SqlTxt VARCHAR(3000); FOR cur AS SELECT TRIM(a.DatabaseName) AS DBNAME, TRIM(a.TABLENAME) AS TABLENAME, TRIM(b.ColumnName) AS ColumnName FROM dbc.Tables a INNER JOIN dbc.COLUMNS AS b ON a.DatabaseName=b.DatabaseName AND a.TABLENAME = b.TABLENAME WHERE a.DatabaseName = :DBNAME AND a.TABLENAME = :TABLENAME AND TableKind = 'T' DO SET SqlTxt = 'SELECT ' || '''' || TRIM(cur.ColumnName) || '''' || ', CASE WHEN SUM(CNT) IS NULL THEN 0 ELSE SUM(CNT) END AS CntNull FROM ( SELECT 0 AS cnt FROM ' || TRIM(cur.DBNAME) || '.' || TRIM(cur.TABLENAME) ||' )a ;'; CALL dbc.sysexecsql(:SqlTxt); END FOR; END;
But when I call the statement I get this error:
CALL Failed.  SUM_COL:SQL statement is not supported within a stored procedure.
How can I fix this?
Not sure why you refer to this as "recursive", but you can't execute a SELECT statement as dynamic SQL; you'd have to use a dynamic cursor for a SELECT.
You could build an INSERT/SELECT instead.
What are you trying to do with the output from the SELECT, return it to the caller? Then build the full output (e.g. INSERT all the rows to one Volatile or Global Temporary table) and open a result set cursor for a SELECT from that table.
BTW - Use DBC.TablesV and DBC.ColumnsV (then you don't need TRIM either).