Example: Passing the Value of for a TOP n Operator to an SQL Procedure - Teradata Vantage - Analytics Database

The following procedure passes the value for n to the TOP n operator in its SELECT request as the integer parameter a.

     CREATE PROCEDURE fct (IN a INTEGER) 
         BEGIN 
             DECLARE hc1 INTEGER; 
             DECLARE hc2 INTEGER; 
             FOR 
                 RecordPointer AS c_sptable CURSOR FOR 
                 SELECT TOP :a x1 AS c_c1, y1 AS c_c2 
                 FROM t1 
                 DO 
                    SET hc1 = RecordPointer.c_c1; 
                    SET hc2 = RecordPointer.c_c2; 
                    INSERT INTO t2 VALUES (:hc1,:hc2); 
             END FOR ; 
         ROLLBACK; 
         END;