Develop generic macros

Database
Enthusiast

Develop generic macros

Hello,

I want to develop generic macros in teradata, for example- Macro that copy tables.

The macro gets input parameters of origin database name,origin table name, target database name, and target table name and copy the table.

I wanted to make something like this (In teradata syntax) -

 

create macro copy_data(origDBName varchar(40), OriginTabName varchar(40), TargetDBName varchar(40, TargetTabName varchar(40), WithData char(1)) as

command varchar(10000);

begin

case (WithData)

when('Y') then

command = "Create table ' || TargetDBName || '.' || TargetTabName || ' as ' || origDBName || '.' || OriginTabName ||' with data and stats;' "

else 

command = "Create table ' || TargetDBName || '.' || TargetTabName || ' as ' || origDBName || '.' || OriginTabName ||' with data and stats;' "

end case;

end;

 

 

Can someone please help me in writing it in the correct syntax?

Thanks a lot

Adi

 

3 REPLIES
Junior Contributor

Re: Develop generic macros

Dynamic table names can't be used in a Macro, you must switch to a Stored Procedure using EXECUTE IMMEDIATE.

Senior Apprentice

Re: Develop generic macros

Hi,

You'll need to write this as a stored procedure, not a macro.

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Develop generic macros

Thanks a lot!

It worked for me!

 

This is how i did it, if someone  is  intrested-

 

 

replace procedure copy_table(in originDBName varchar(40),
in OriginTabName varchar(40),
in TargetDBName varchar(40),
in TargetTabName varchar(40),
in WithData char(1))
begin
declare mycommand varchar(10000);
case (WithData)
when('Y') then
set mycommand = 'Create table ' || TargetDBName || '.' || TargetTabName || ' as ' || originDBName || '.' || OriginTabName ||' with data and stats;';
else
set mycommand = 'Create table ' || TargetDBName || '.' || TargetTabName || ' as ' || originDBName || '.' || OriginTabName ||' with no data;';
end case;
execute immediate mycommand;
end;