In one of our older database tables we have a field named “ROLLUP”.
For SQL SERVER 2008 this is a reserved word. When writing dynamic SQL with group by statements using the column name “rollup” coldfusion will throw a syntax error indicating there is a problem near “rollup” or “,”.
The solution is to replace the keyword with the keyword in brackets.
Example
CFSET mylist = “rollup,column2,column3,column4”
* this may come from form elements passed or other selections that make it a dynamic list
Query:
Select #mylist#
, sum myValue
from myTable
where 1=1
group by #rereplace(mylist, “rollup”, “[rollup]”, “1”)#
order by #mylist#
Yields
group by [rollup],column2,column3,column4
