For eg :
replace macro database1.macro1(databasename char(50),tablename char(50))
Select count(*) from :databasename.:tablename;
Can someone let me know how to fix this issue ?
REPLACE MACRO Failed.  Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword or '(' between the 'from' keyword and ':'.
No, you cannot pass an object name as a macro parameter. Macro parameters cannot be used as database object names.
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.
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 ..
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;