0

I have a store procedure in teradata like following that I dont know why it does not work:

Replace procedure dt.tablename (param1 varchar(100),param2 varchar(100))

Begin

declare sqlstr varchar(500);

set sqlstr= 'CREATE VOLATILE TABLE test1 AS (SELECT * FROM dt.tablename2) with data primary index on commit perservce rows;'; execute immediate sqlstr;

--Note: up to here, it runs ok. It did create test1

insert into dt.tablename3 (var1,var2) select :param1 as var1, :param2 as var2 from test1;

end;

I am using dbever, it will popup bind parameters panels but even if i filled value in the panels, it still shows error.

Can anyone explain to me why this query won't run properly? I also don't know how to show errors. If anyone can also share some resource to learn that, that would be great. Thank you.

ukiwuno
  • 1
  • 1
  • When the procedure was successfully created and DBeaver asks for parameters you CALLed the SP with placeholders -> it's a DBeaver feature. What is the actual error message returned? – dnoeth May 25 '23 at 08:43
  • What is the intent here? The SELECT part of the INSERT is querying FROM the volatile table, but you are supplying input variables as values in the expression list. You can INSERT VALUES and reference the values of the variables, but if you want the input value text returned by the SELECT part you probably need to make the INSERT/SELECT a dynamic statement also and concatenate the input text values (with surrounding quotes) to make them literals in the SQL. – Fred Jun 01 '23 at 16:23

0 Answers0