title: Insert Into table Teradata dynamic stored procedure SQL
body: <p>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 Select statements is the SQL that I need to be dynamic and this is where I run into problems.</p>
  
  <p>Here is my SQL:</p>
  
  <pre><code>CREATE PROCEDURE "mydb"."test_sp20" (DepID integer) --DepID is my parameter
  DYNAMIC RESULT SETS 1 SQL SECURITY OWNER
  BEGIN
  
  DECLARE q1 VARCHAR(10000);
  DECLARE cur1 CURSOR WITH RETURN ONLY TO client FOR s1;
  
  CREATE VOLATILE TABLE mydb.tbl_1 , no fallback, no log(
  Consumer_Unit_Id    INTEGER, 
  Price_Promotion_Id  INTEGER, 
  Promotion_Id    INTEGER)
  primary index (Consumer_Unit_Id, Price_Promotion_Id, Promotion_Id) on commit preserve rows ;
  
  INSERT INTO mydb.tbl_1
  SELECT * FROM mydb.tbl_1
  SET q1 = 'Select * from mydb.tbl_1'
  PREPARE s1 FROM q1;
  OPEN cur1;
  END;
  </code></pre>
  
  <p>This works fine as a SP with static SQL but I need the Select statements to be dynamic in that within them I have Department and Sections parameters that I want to be able to pass accross. e.g.</p>
  
  <pre><code>INSERT INTO mydb.tbl_1
  SQL = 'Select * from mydb.tbl_1 where Department_ID = ' || DepID ||'
  </code></pre>
  
  <p>I've also tried:</p>
  
  <pre><code>SQL = 'INSERT INTO Select * from mydb.tbl_1 where Department_ID = ' || DepID ||'
  </code></pre>
  
  <p>In both cases I get the following error:</p>
  
  <pre><code>7683:  TEST_SP20:Invalid statement specified inside a dynamic declare cursor/SQL statement
  </code></pre>
  
  <p>I seem to be able to have a dynamic SQL string but I can't have a INSERT INTO SELECT string??</p>
  
  <p>@dnoeth helped me get this started so any more help is much appreciated.</p>