Recursive select statement in stored procedure

General
Highlighted
Enthusiast

Recursive select statement in stored procedure

Hi,

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.  [5568] SUM_COL:SQL statement is not supported within a stored procedure.

 

How can I fix this?

6 REPLIES
Teradata Employee

Re: Recursive select statement in stored procedure

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).

Enthusiast

Re: Recursive select statement in stored procedure

Hi Fred,

 

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?

 

 

 

Teradata Employee

Re: Recursive select statement in stored procedure

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.)

Enthusiast

Re: Recursive select statement in stored procedure

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.

 

Teradata Employee

Re: Recursive select statement in stored procedure

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.

 

Enthusiast

Re: Recursive select statement in stored procedure

Thanks a lot for your help!