Coldfusion working around SQL Server Key words

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.

CFSET mylist = “rollup,column2,column3,column4”
* this may come from form elements passed or other selections that make it a dynamic list

Select #mylist#
, sum myValue
from myTable
where 1=1
group by #rereplace(mylist, “rollup”, “[rollup]”, “1”)#
order by #mylist#

group by [rollup],column2,column3,column4

Cup size   
Select size then click on coffee cup.
This entry was posted in Coldfusion, Problems & Fixes, Snippets, SQL. Bookmark the permalink.