17.10 - Example: Creating a Procedure with Dynamic SQL Using the SQL PREPARE Statement - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1144-171K
Language
English (United States)

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;