Insert Into Table Teradata Dynamic Stored Procedure SQL

I am trying to create a Stored Procedure in Teradata that will accept various arguments. My query has 4 passes of SQL where it creates 3 lots of volatile tables. Within the Selec

Solution 1:

You didn't tell that you want to INSERT/SELECT using Dynamic SQL, a cursor is only needed when you want to return a result set from your SP.

DECLARE q1 VARCHAR(10000);

SET q1 = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ' || TRIM(DepID) ||';';

EXECUTE IMMEDIATE q1;

But if only the values passed to the WHERE-condition are supposed to be dynamic you might better apply a prepared statement (which could be used multiple times):

SET q1 = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ?;';

PREPARE stmt FROM q1;

EXECUTE stmt USING DepID;