[SA 9.1.276 Pro] - macro variables with text pre/suffix

[SA 9.1.276 Pro] - macro variables with text pre/suffix

Text that is placed next to eg. $COLUMN$ macro will be appended to the expanded value, thus making things like

	Code:
	DECLARE @$COLUMNS(vertical,types)$

work in SQL Server. The same doesn't work in MySQL which requires each variable to have its own DECLARE keyword, thus it would need the keyword DECLARE itself with an additional space to be attached to the macro. If I recall correctly, that could be achieved by using double quotes like this:

	Code:
	"DECLARE lv_"$COLUMNS(vertical,types)$;

But this no longer works. Could you check it, please?

Unfortunately I get an exception when executing that kind of code snippet in my environment. I'm sending it to the support team for further investigation.

I believe that regression has been fixed in 9.2. I have found a ticket with similar description.

This has improved significantly in [SA 9.2.346 Pro], though there's one small side-effect. I've created the following snippet:

	Code:
	"DECLARE lv_"$COLUMNS(vertical,types)$";" SELECT s.$COLUMNS$ INTO lv_$COLUMNS$ FROM $OBJECT(ins_qualname, table, view)$ AS s WHERE 1 = 1 AND s\| ;

The code it generates for a selected example table is:

	Code:
	DECLARE lv_id INT(11);, DECLARE lv_name VARCHAR(128);, DECLARE lv_description VARCHAR(128);, DECLARE lv_area VARCHAR(4096);, DECLARE lv_attributes VARCHAR(4000);, DECLARE lv_calendarid INT(11); SELECT s.id ,s.name ,s.description ,s.area ,s.attributes ,s.calendarid INTO lv_id ,lv_name ,lv_description ,lv_area ,lv_attributes ,lv_calendarid FROM traccar.geofences AS s WHERE 1 = 1 AND s;

I'm almost there. The result has surplus comas at the end of each DECLARE statement. I believe its the inherent/inherited property of the $COLUMN()$ macro to paste a comma after each (before the last) item it has retrieved, except when it has " AND" or " OR" attached as a suffix. Is there a way to prevent those comas being put there?

	Quote:
	I'm almost there. The result has surplus comas at the end of each DECLARE statement. I believe its the inherent/inherited property of the $COLUMN()$ macro to paste a comma after each (before the last) item it has retrieved, except when it has " AND" or " OR" attached as a suffix. Is there a way to prevent those comas being put there?

You're correct, the commas after column names or their optional suffixes is a feature of $COLUMN()$ macro, they are always added, unless the suffix includes " AND" or " OR". The only solution I know and it's kind of ugly is masking extra commas with a line comments

	Code:
	"DECLARE lv_"$COLUMNS(vertical,types)$"; --"

or tricking it into thinking that AND is used

	Code:
	"DECLARE lv_"$COLUMNS(vertical,types)$"; -- and more " no more

That's some clever hacking, thank you very much.