[9.0.162 Pro] $OBJECT$ Macro adding last selected column?

[9.0.162 Pro] $OBJECT$ Macro adding last selected column?

the cfetch snippet:

	Code:
	DECLARE @$COLUMNS+TYPES$ DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR SELECT $COLUMNS$ FROM $OBJECT$ OPEN my_cursor\| FETCH FROM my_cursor INTO @$COLUMNS$ WHILE @@FETCH_STATUS = 0 BEGIN /{ ... Cursor logic here ... }/ FETCH FROM my_cursor INTO @$COLUMNS$ END CLOSE my_cursor DEALLOCATE my_cursor

In this case, it lets you select columns and so on right?

Well the table name ends up with the LAST column selected even if I use $OBJECT(table)$

http://screencast.com/t/Hg7p4Y7ryPX1

This is not the intended usage of the cftetch snippet. The snippet expects that you choose an object name., for example, a table name and not a subject of its column. Column selection returns results incompatible with the snippet code. If you want to it to work with a subset of columns, you will need to modify the snippet code.

That's a piece of cake, all you have to do is to replace $OBJECT$ with an $$...$$ that analyzes the passed code for object given by $OBJECT$ and returns the actual object name accordingly. Also, it doesn't hurt to filter out stuff with $OBJECT(xyz)$ that wouldn't work as cursor material anyway.

Code:

DECLARE @$COLUMNS+TYPES$

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT $COLUMNS$
FROM $$
DECLARE @object SYSNAME = '$OBJECT(table, view, mview, tblFunc)$';
SELECT
CASE
WHEN PARSENAME(@object ,3) != '' THEN PARSENAME(@object ,3) + '.' + PARSENAME(@object ,2)
ELSE PARSENAME(@object ,2) + '.' + PARSENAME(@object ,1)
END;
$$

OPEN my_cursor|

FETCH FROM my_cursor INTO @$COLUMNS$

WHILE @@FETCH_STATUS = 0
BEGIN
/*{ ... Cursor logic here ... }*/

FETCH FROM my_cursor INTO @$COLUMNS$
END

CLOSE my_cursor
DEALLOCATE my_cursor

EDIT:Or a shorter version:

Code:

DECLARE @$COLUMNS+TYPES$

DECLARE my_cursor CURSOR FAST_FORWARD READ_ONLY FOR
SELECT $COLUMNS$
FROM $OBJECT(ins_schema, table, view, mview, tblFunc)$.$OBJECT(ins_object, table, view, mview, tblFunc)$

OPEN my_cursor|

FETCH FROM my_cursor INTO @$COLUMNS$

WHILE @@FETCH_STATUS = 0
BEGIN
/*{ ... Cursor logic here ... }*/

FETCH FROM my_cursor INTO @$COLUMNS$
END

CLOSE my_cursor
DEALLOCATE my_cursor

	SysOp wrote:
	This is not the intended usage of the cftetch snippet. The snippet expects that you choose an object name., for example, a table name and not a subject of its column. Column selection returns results incompatible with the snippet code. If you want to it to work with a subset of columns, you will need to modify the snippet code.

Shouldn't there be a way to tell that? I'm never writing cursors that use a whole set of columns. In fact I'm relatively sure nobody is. If it's not meant to use then maybe there should be a way to tell the snippet or object macro to disallow drilling down into the column picker. $Object(table)$ seems to get close as you can no longer pick out columns but you still can drill down.