Example: Creating a Procedure with Dynamic SQL Using the SQL PREPARE Statement - Teradata Vantage - Analytics Database

The following CREATE PROCEDURE request creates a procedure that contains dynamic SQL using a PREPARE statement:

     CREATE PROCEDURE abc (IN data1v VARCHAR(10), 
                           IN data2v VARCHAR(10))
        DYNAMIC RESULT SETS 1
       BEGIN
         DECLARE sql_stmt1 VARCHAR(100);
         DECLARE sales DECIMAL(8,2);
         DECLARE item INTEGER;
         DECLARE cstmt CURSOR WITH RETURN FOR stmt1;
         SET sql_stmt1 = 'SELECT  T1.item, T1.sales FROM T1 WHERE'
               data1v | '= store_name AND '   | data2v | '= region;';
         PREPARE stmt1 FROM sql_stmt1;
         OPEN cstmt;
         FETCH NEXT FROM cstmt INTO item, sales;
       END;

The PREPARE can also be written as follows using parameter markers as seen in the following procedure code fragment:

         SET sql_stmt1 = 'SELECT  t1.item, t1.sales FROM t1 WHERE ?' 
                         '= store_name AND ? = region;';
         PREPARE stmt1 FROM sql_stmt1;
         OPEN cstmt USING data1v, data2v;
         FETCH NEXT FROM cstmt INTO item, sales;