All Forums Database
kvnc 2 posts Joined 09/14
04 Sep 2014
Help with macro

I wonder if possible to pass a long string of multiple conditions thru macro. Reason is because the combination of conditions varies upon the business rules. Another reason is unfortunately I have no permission to create procedure in TD.
-- original query condition
WHERE SLS_GRP='2305' AND BUS_NM LIKE '%FLO%' AND LOC_AT_CNTRY_NM LIKE '%UNITED%STATE%' AND CURR_MRCH_IND='Y'
-- in macro
CREATE MACRO T(MLOGIC VARCHAR(256)) AS (
....
WHERE SLS_GRP = :MLOGIC
;);
EXEC T('''2305'' AND BUS_NM LIKE ''%FLO%'' AND CURR_MRCH_IND=''Y''');
I have tested that the macro above doesn't work. Seems like the whole string is used against SLS_GRP rather than separate multi-conditions. Does anybody know how to make multiple conditions as one input variable in macro?
Thanks,
 

Tags:
dnoeth 4628 posts Joined 11/04
05 Sep 2014

There's no way to do it in a macro.
In every DBMS you will need some Dynamic SQL to do so and a DBA will never allow that due to possible SQL Injection, e.g.

 EXEC T('''2305'' AND BUS_NM LIKE ''%FLO%'' AND CURR_MRCH_IND=''Y'' OR 1=1');

 

Dieter

tomnolan 594 posts Joined 01/08
05 Sep 2014

I agree with Dieter.
 
You need to use the right tool for the job. If you need to vary your WHERE-clause conditions significantly, then a macro isn't the right choice for you.
 
Instead, you should simply execute the query itself, and vary the WHERE-clause condition.

kvnc 2 posts Joined 09/14
05 Sep 2014

I thought macro won't work but hope you guys may have secret sauce to make it work... Anyway, what about procedure? May I pass the where-clause conditions to a sql_stmt and then execute it immediate? 

Raja_KT 1246 posts Joined 07/09
05 Sep 2014

If you are using unix, then you can combine database script and unix script, making  different calls to parameters you want based on your conditions.
For example, in one of my previous projects, we generate DMLs querying dictionary tables based on different parameters and exported to  files on the fly. The files are then executed in the subsequent steps.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Renji 17 posts Joined 08/05
29 Oct 2014

​​You should be able to define the macro with multiple parameters, and pass the values to the Macro in separate fields. For ex

 

CREATE MACRO T (SLS_GRP VARCHAR (10), BUS_NM  VARCHAR (10), CNTRY_LOC VARCHAR (10),,,) 

and EXEC T(var01, var02, var03) etc, and in the scrit you can assign each to its own variables and use in the query.

 

Regards
John Abraham

You must sign in to leave a comment.