Can we pass object as parameter in a macro ?

Database

Can we pass object as parameter in a macro ?

For eg : 

replace macro database1.macro1(databasename char(50),tablename char(50))

as

(

Select count(*) from :databasename.:tablename;

);

Can someone let me know how to fix this issue ? 

REPLACE MACRO Failed.  [3707] Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'from' keyword and ':'.

6 REPLIES

Re: Can we pass object as parameter in a macro ?

Try execute immediate.

Teradata Employee

Re: Can we pass object as parameter in a macro ?

No, you cannot pass an object name as a macro parameter. Macro parameters cannot be used as database object names.

Teradata Employee

Re: Can we pass object as parameter in a macro ?

Macro parameters are designed for constant substitution only. They can appear only in the places in a statment where a constant value can appear (and not every one of those either - like options in DDL.)

To fill in object names, IN lists, WHERE clauses, ORDER BY lists,... one needs to be able to do text substitution and string manipulation. To do this, one needs to use a stored procedure where one can do any kind of desired string manipulation to create the text of the SQL statement, then execute the resulting string.

Re: Can we pass object as parameter in a macro ?

thanks Todd .. after your suggestion i tried with stored procedure .. but still i m a bit confused how to implement it with stored procedure.. can u please guide me ?

I m trying it .. but i m getting errors .. 

REPLACE PROCEDURE sysdba.new_date2 (tablename1 varchar(200),databasename1 varchar(200))

DYNAMIC RESULT SETS 2

BEGIN

declare create_user_sql char(500);

DECLARE date1 CURSOR WITH RETURN ONLY FOR

sel  tablename from dbc.tables where tablename=tablename1 and databasename=databasename1;

open date1;

set create_user_sql='sel count(*) from '||databasename1||'.'||tablename1||';';

call dbc.sysexecsql(create_user_sql);

END;

Here i guess when i use sel statement in sysexecsql it is not accepting .. Could you please provide a solution for this ?

Teradata Employee

Re: Can we pass object as parameter in a macro ?

You can't use SysExecSQL for a data-returning statement. You need to use a second (dynamic) cursor:

DECLARE sel_csr CURSOR FOR sel_stmt;

PREPARE sel_stmt FROM create_user_sql;

OPEN sel_csr;

Re: Can we pass object as parameter in a macro ?

Thanks Fred.. I was able to achieve through stored procedure.. thanks for your support