How to Execute Dynamic SQL in Teradata

How to execute dynamic SQL in Teradata

You may find success putting this in a stored procedure using the DBC.SysExecSQL command.

Here is an overly simplified example of a stored procedure in Teradata. Obviously in production would want an error handler defined to address things like invalid database objects. Furthermore, you could return the SQLSTATE back as a parameter to test for whether the stored procedure completed successfully or not.

CREATE PROCEDURE SYSDBA.CommentDatabase
(
  IN P_Database VARCHAR(30),
  IN P_Comment VARCHAR(255), 
  OUT MSG
)
MAIN:  --Label
BEGIN

  DECLARE  P_SQL_TEXT     VARCHAR(4000);

  SET P_SQL_TEXT='COMMENT ON DATABASE '||P_DATABASE||' AS '''||P_COMMENT||'''';
  CALL dbc.SysExecSQL (:P_SQL_TEXT);

  SET MSG = 'Database '||P_DBNAME||' commented successfully!';
END;

How to write a dynamic SQL code in Teradata?

The pair of parens starting before the BEGIN should be removed and all single quotes around strings including the dates must be doubled:

REPLACE PROCEDURE NPVAZ_CVM_CHECK_TEST_CASE (IN COL CHAR(50))
BEGIN
CALL DBC.SYSEXECSQL
('
INSERT INTO PROD_CE_WORK_SPACE.NPVAZ_CVM_CHECK_TEST_CASE_5
                            SELECT
                            '||COL||' AS COLMN,
                            PW_END_DATE,
                            ''ACPT'' AS TAB,
                            MIN( '||COL||') AS PER_MIN,
                            MIN(CASE WHEN SEQNUM / 0.25 >= CNT THEN  '||COL||' END) AS PER_25,
                            MIN(CASE WHEN SEQNUM / 0.50 >= CNT THEN  '||COL||' END) AS PER_50,
                            MIN(CASE WHEN SEQNUM / 0.75 >= CNT THEN  '||COL||' END) AS PER_75,
                            MAX( '||COL||') AS PER_MAX
                            FROM (
                                        SELECT PC.*,
                                         ROW_NUMBER() OVER (ORDER BY  '||COL||') AS SEQNUM,
                                         COUNT(*) OVER () AS CNT
                                          FROM PROD_EXP_DL_CVM.ACPT_PROD_CVM PC
                                          WHERE PC.PW_END_DATE = DATE ''2017-01-17''
                                         )A
                            GROUP BY 1,2,3

            UNION ALL

                            SELECT 
                            '||COL||' AS COLMN,
                            PW_END_DATE,
                            ''PROD'' AS TAB,
                            MIN( '||COL||') AS PER_MIN,
                            MIN(CASE WHEN SEQNUM / 0.25 >= CNT THEN  '||COL||' END) AS PER_25,
                            MIN(CASE WHEN SEQNUM / 0.50 >= CNT THEN  '||COL||' END) AS PER_50,
                            MIN(CASE WHEN SEQNUM / 0.75 >= CNT THEN  '||COL||' END) AS PER_75,
                            MAX( '||COL||') AS PER_MAX
                            FROM (
                                SELECT PC.*,
                                 ROW_NUMBER() OVER (ORDER BY  '||COL||') AS SEQNUM,
                                 COUNT(*) OVER () AS CNT
                                  FROM PROD_EXP_DL_CVM.PROD_CVM PC
                                  WHERE PC.PW_END_DATE =  DATE ''2017-01-17''
                                )B
                          GROUP BY 1,2,3
');

END;

Dynamic SQL in Teradata Stored Procedure using conditionals

You can do one thing. First create variables for all your input parameters like this :

SET lv_pAcctNum  = CASE WHEN 'All' IN (''||pAcctNum ||'') THEN 1 ELSE (''||pAcctNum ||'') END;

Here 'All'signifies the entires where user has not entered anything (it could be null also as per your procedure design ).

After that in the dynamic sql where clause, use the variable like this :

'WHERE CASE WHEN ''All'' IN ('''||pAcctNum||''') THEN 1 ELSE Route_to_Market END IN  ('''||lv_pAcctNum||''' )'

This will achieve your goal

Teradata Dynamic SQL with Bind Variables?

DML statements with bind variables are usually executed multiple times, thus there are no bind variables when you use EXECUTE IMMEDIATE, you must use EXECUTE/PREPARE:

DECLARE SQLstring VARCHAR(1000);
SET SQLstring = 'update MyTable SET ColumnA = ? where columnB = ?'; -- ? identify bind variables
PREPARE S1 FROM SQLstring;
EXECUTE S1 USING variable1, variable2;

See the Stored Procedure manual

---

Related Topics