All Forums UDA
bramam 1 post Joined 11/07
12 Nov 2007
How to create a Macro passing table names as the parameter

I am writing sql for 2 different lines of business. Everything in the code is the same except for 2 table names (where I'm selecting from and inserting into).I would like to use the macro but it doesn't work with how I'm writing it. Any suggestions? This would help so much on maintenance becuase everything is the same except for the 2 table names.Here's what I have so far that isn't working.CREATE MACRO CRTCL (RPT_TABLE VARCHAR(40), SO_TABLE1 VARCHAR(40)) as (INSERT INTO hs_rpt_tbls.tps_work2SELECT RP.per_end_dt, BE.rgn_no, BE.assigned_ctr, SOSP.tps_spc_cd,SOSP.tps_idy_cd,SOSP.so_typ_cd,count(S OSP.so_dt)FROM :SO_TABLE1 SOSP,(SELECT per_end_dt, acctg_yr_sta_dt from hs_db.rptg_per group by 1,2) as RP,(SELECT rgn_no, assigned_ctr, un_no FROM hs_db.bugroup by 1,2,3) as BEWHERESOSP.so_crt_dt between RP.acctg_yr_sta_dt and RP.per_end_dtAND SOSP.un_no=BE.un_noGROUP BY 1, 2,3,4,5,6;);I would execute it using the following:EXECUTE CRTCL (‘dbname.so_table', ‘dbname.final_table);

dnoeth 4628 posts Joined 11/04
21 Nov 2007

You can't pass any database/table/column-name as parameter to macros.The only way is a Stored Procedure using Dynamic SQL.Dieter

Dieter

Priti_Kanal 8 posts Joined 05/16
20 Jun 2016

Hi Dieter,
I am having requirement where I want to use database_name and table_name as parameterized in stored procedure.
In procedure I will be performing below DML operations :
 
INSERT INTO database_name.table_name
SELECT * FROM database_name.table_name;
 
UPDATE database_name.table_name
SET FLAG='Y'
WHERE DATE_1=DATE '1900-12-01';
 
How this can be achieved using dynamic SQL?
 
Thanks in advance..!!
 

dnoeth 4628 posts Joined 11/04
20 Jun 2016

http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/SQL_Reference/B035_1148_151K/ch03.108.114.html#ww11214369
 
Of course you should always be aware of possible SQL Injection:
https://en.wikipedia.org/wiki/SQL_injection

Dieter

You must sign in to leave a comment.