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).
I tried to use a dynamic cursor but then when I call it and give as an input the name of the table I get an error.
I need to get the outupt of the select with all possible column names and add a UNION between them (just realized that I forgot to put that part) until there are no more columns.
Following your suggestion, my plan right now is to:
1) create a GTT with all the statements I need to concatenate (so, SELECT column 1... UNION ALL, SELECT column 2)
2) concatenate all rows into one row
3) fecth this result as SqlTxt.
I'm a bit lost on the last point. Any suggestions?
Is the output of the SP supposed to be the SELECT statement text? Or is it supposed to be column names and some associated value? If the latter, what does the value represent?
(In the original SQL, CntNull would always be zero.)
Yes, it is supposed to be the select statement text. It's a control query that I use against a similar check for null values.
You could concatenate into one long string, insert that to a GTT / Volatile table, and pass that back via a result set cursor. But that's extra work; since SQL statements can be multiple lines I would just do something like this (based on your original post):
REPLACE PROCEDURE sum_col ( IN TABLENAME VARCHAR(128), DBNAME VARCHAR(128) ) DYNAMIC RESULT SETS 1 BEGIN DECLARE cur CURSOR WITH RETURN FOR SELECT 'SELECT ''' || c.ColumnName || ''' as ColumnName,' || 'expression text for this column'
||' FROM ' || c.DatabaseName || '.' || c.TableName
|| 'group by, etc. for this column'
||CASE WHEN ColumnId = (SELECT MAX(ColumnId) from dbc.ColumnsV where DatabaseName=:DatabaseName and TableName=:TableName) THEN ';' ELSE ' UNION ALL ' END as StmtTxt FROM DBC.ColumnsV c WHERE DatabaseName=:DatabaseName and TableName=:TableName ORDER BY ColumnId ; OPEN cur; END;
You could also sort alphabetically or whatever, as long as you adjust the CASE statement to recognize the "last" column.