3

ColdFusion uses #'s to delineate variables. In SQL Server a table name with # or ## is temp table and a global temp table respectively. How does one use SQL Temp tables in ColdFusion?

Leigh
  • 28,765
  • 10
  • 55
  • 103
James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • You could also switch from a temp table to a CTE Common Table Expression. I find myself using these more than temp tables especially in CF code. The answer above was perfect! –  Dec 02 '12 at 07:45

2 Answers2

3

ColdFusion treats shows ## as a single # in the output. Hence

<cfquery name="qryTempUser">
SELECT * 
FROM   ##tempUsers
WHERE  Active_CODE = 1
</cfquery>

is pulling from a temp table called #tempUsers

<cfquery name="qryTempUser">
SELECT * 
FROM   ###myTableName#
WHERE  Active_CODE = 1
</cfquery>

is pulling from a temp table called that is specified in a ColdFusion variable called myTableName

<cfquery name="qryTempUser">
SELECT * 
FROM   ####tempUsers
WHERE  Active_CODE = 1
</cfquery>

is pulling from a global temp table called ##tempUsers

<cfquery name="qryTempUser">
SELECT * 
FROM   #####myTableName#
WHERE  Active_CODE = 1
</cfquery>

is pulling from a global temp table called that is specified in a ColdFusion variable called myTableName

James A Mohler
  • 11,060
  • 15
  • 46
  • 72
  • nice followup to http://stackoverflow.com/questions/13666883/date-comparison-in-ms-sql-2005 – Russ Dec 02 '12 at 07:37
-1

Another alternative is to use stored procedures. You have to write all that t-sql somewhere and a stored procedure might make it easier for you.

Dan Bracuk
  • 20,699
  • 4
  • 26
  • 43