Parameters and teradata Dynamic SQL

Tools

Parameters and teradata Dynamic SQL

I'm trying to use a parameter within my dynamic sql query, but just can't get it to work. I simplified my code to highlight only the part I'm struggling with.

I first create 't1', to which I insert the record.

create volatile table t1 as(

select date '1900-01-01' as date_col

)with data on commit preserve rows;

First procedure works fine, and insert the given date (2014-01-01).

replace procedure mi_table.dynamic_param()

begin

CALL DBC.SYSEXECSQL(

'insert into t1

select date '||'''2014-01-01'''||' ;'

);

end;

What I really want to do and doesn't work, is something as such:

replace procedure mi_table.dynamic_param() begin

declare max_avail_date date;

set max_avail_date = date '2014-01-01' ;

CALL DBC.SYSEXECSQL(

'insert into t1 select '||:max_avail_date||' ;'

);

end;

which doesn't work. I tried what seems to be all possible combinations of possible ways to write the parameter into my dynamic sql, but it never worked.

Suggestions anyone? Thanks!