Teradata stored procedure with dynamic parameters

Question

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?

Answer 1

Explanation: This error occurs when in one of the following situations: a. An unsupported SQL statement is specified in a stored procedure using the dynamic SQL feature of stored procedures. For instance, DATABASE statement, SET SQL statements, multi statement requests, CREATE USER/DATABASE statement without FROM clause, SELECT - INTO SQL or CALL SQL. This is a run-time error.

Looks like you have other syntax errors. Declare your cursor, open your cursor, etc.. I think you can solve your goal in an easier way, what exactly are you trying to accomplish?