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

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

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;