Recursive select statement in stored procedure

General
Highlighted
Visitor

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?

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