title: WHILE loop in Teradata procedure
body: <p>I'm trying to write a procedure that concatenates all rows in a table in the case in which the row number is unknown.</p>
  
  <p>I have this code but it is not working. </p>
  
  <pre><code>CREATE PROCEDURE Test (OUT r VARCHAR(3000))
  
  BEGIN
  DECLARE RowCnt INT;
  DECLARE CurrRow INT ;
  SET CurrRow = 1,
         r = 'SELECT ', 
         RowCnt = (SELECT COUNT(*) 
                     FROM tableWithSQLStmnts
                     )    
  WHILE CurrRow &lt;= RowCnt DO
  BEGIN 
             SET r = r + 
             CASE WHEN CurrRow = 1  
                  THEN 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + ' 
                                   THEN SqlStmnt 
                                              ELSE SPACE(0) END ) + ' + CHAR(13) 
             WHEN i = RowCnt 
               THEN 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + ' 
                                   THEN ''  '' + SqlStmnt 
                                   ELSE SPACE(0) END ) ' + CHAR(13) 
               ELSE 'MAX( CASE Seq WHEN ' + CAST( CurrRow AS VARCHAR ) + ' 
                                   THEN ''  '' + SqlStmnt
                                   ELSE SPACE(0) END ) + ' + CHAR(13)  
             END 
             SET CurrRow = CurrRow + 1 ;
  END ;
  SET r = r + ' 
      FROM ( SELECT SqlStmnt, 
                    ROW_NUMBER() OVER ( PARTITION BY TabName ORDER BY SQlStmnt )
               FROM tableWithSQLStmnts t ) D ( SqlStmnt, Seq ) 
             GROUP BY TabName;' 
  
  END WHILE;
  END
  
  ;
  </code></pre>
  
  <p>I'm getting the following errors:</p>
  
  <ul>
  <li>Syntax error, expected something like ';' between an integer and ','.'.</li>
  <li>Unexpected text 'SET'.</li>
  </ul>
  
  <p>New code, as suggested by dnoeth.</p>
  
  <pre><code>REPLACE PROCEDURE Test3 (IN TbName VARCHAR(256)) --, OUT r2 VARCHAR(3000))
  
  BEGIN
  DECLARE RowCnt INT;
  DECLARE i INT;
  DECLARE CurrRow INT;
  DECLARE r VARCHAR(3000);
  DECLARE r2 VARCHAR(3000);
  SET CurrRow = 1;
  SET r = 'SELECT ';
  SET RowCnt = (SELECT COUNT(*) 
                FROM tableWithSQLStmnts
                WHERE tabname = :TbName
               );
  
  WHILE CurrRow &lt;= RowCnt DO
     BEGIN 
        SET r = r ||
     'MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || ' 
             THEN '' , '' || SqlStmnt
             ELSE '''' END ) 
     '
        || CASE WHEN CurrRow = RowCnt 
                THEN '' 
                ELSE ' ||  '
           END;
        SET CurrRow = CurrRow + 1 ;
     END;
  END WHILE;
  
  SET r = r || ' 
      FROM ( SELECT SqlStmnt, 
                    ROW_NUMBER() OVER ( PARTITION BY TbName ORDER BY SQlStmnt )
               FROM tableWithSQLStmnts t ) D ( SqlStmnt ) 
             GROUP BY TbName
             ;';
  
  SET r2 = r;
  CALL dbc.sysexecsql(:r);
  END;
  </code></pre>
  
  <p>Now I get this error:</p>
  
  <pre><code>[3706] Syntax error: Column name list shorter than select list.
  </code></pre>
  
  <p>EDIT 2:</p>
  
  <p>I have now rewritten it like this:</p>
  
  <pre><code>REPLACE PROCEDURE Test3 (IN TabName VARCHAR(256))
  DYNAMIC RESULT SETS 1
  BEGIN
  DECLARE RowCnt INT;
  DECLARE Seq INT;
  DECLARE QRY VARCHAR(3000);
  DECLARE CurrRow INT;
  SET QRY= 'INSERT INTO vt21 SELECT   ';
  SET CurrRow = 1;
  
  CREATE VOLATILE TABLE vt21(QRY VARCHAR(3000)) ON COMMIT PRESERVE ROWS;
  SET RowCnt = (SELECT COUNT(*) 
                FROM TestTable
                WHERE tabname = :TabName
               );
  FOR CurrentRefRow AS SourceCursor CURSOR FOR   
              SELECT SqlStmnt
              FROM TestTable
              DO
  WHILE CurrRow &lt;= RowCnt 
  DO
     BEGIN 
        SET QRY = QRY ||
        CASE  WHEN CurrRow=1
        THEN   'MAX( CASE Seq  WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || ' 
             THEN '' , '' || SqlStmnt
             ELSE '''' END )     ' 
  
           WHEN CurrRow &lt; RowCnt
           THEN ', MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || ' 
             THEN '' , '' || SqlStmnt
             ELSE '''' END )    ' 
           WHEN CurrRow=RowCnt
        THEN   ', MAX( CASE Seq WHEN ' || CAST( CurrRow AS VARCHAR(10) ) || ' 
             THEN '' , '' || SqlStmnt
             ELSE '''' END ) '
                ELSE ' ||  '
           END;
                    SET CurrRow = CurrRow + 1 ;
           END;
           END WHILE;
  
  SET QRY = QRY || ' 
      FROM ( SELECT SqlStmnt, Tabname,
                    ROW_NUMBER() OVER ( PARTITION BY TabName ORDER BY SQlStmnt )
               FROM TestTable t ) D ( Seq, Tabname, SqlStmnt ) 
             GROUP BY TabName
             ;';
  
  EXECUTE IMMEDIATE QRY;
  END FOR;   
  
  
   BEGIN -- return the result set
        DECLARE resultset CURSOR WITH RETURN ONLY FOR S1;
        SET QRY = 'SELECT * FROM  vt21;';
        PREPARE S1 FROM QRY;
        OPEN resultset;
     END;
  
  DROP TABLE vt21;
  END;
  </code></pre>
  
  <p>But I'm getting the following error:</p>
  
  <p>CALL Failed.  [3813] The positional assignment list has too many values.</p>
  
  <p>I have tried modifying it but when I delete one value than it says that column name list is longer then the select list.</p>