Insert into SQL Temp table with cfqueryparam

[stevehn]

Has anyone successfully inserted values into SQL temp table using cfqueryparam and able to called from it?

The below codes work just fine and only failed when replaced

values ('myValue')  with  values (<cfqueryparam value="myValue" cfsqltype="cf_sql_varchar" >)

Appreciated if someone can take a look and let me know what is going on.

<cfquery name="myQry" datasource="myDSN" >

  CREATE TABLE ##tmpTable
  (
  column1 nvarchar(50)
  )

  insert into ##tmpTable (column1) values ('myValue')

</cfquery>

<cfquery name="tmpTableQry" datasource="myDSN" >
  select colum1 from ##tmpTable
< /cfquery>

<cfdump var="#tmpTableQry#">

CF11

[Carl Von Stetten · Accepted solution]

Near as I can figure from Microsoft's documentation, you can't use prepared statements with temporary tables.  From SQL Server 2014 docs:

In SQL Server 2005, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Not entirely on point, but close.

I ran the code sample you provided on ColdFusion 2016 and SQL Server 2012 (that's what I have) without the <cfqueryparam> and as you noted it works.  Then I added the <cfqueryparam> which causes the query to be processed by JDBC as a prepared statement, and it failed.  Then I turned on SQL Profiler and ran both ways again.  When run with the <cfqueryparam>, the whole first <cfquery> statement never even gets passed into SQL Server.  Only the second <cfquery> runs, but throws an error because the temporary table doesn't exist since the first <cfquery> didn't run.  It looks to me like attempting to run the <cfqueryparam> version of the query gets swallowed by the JDBC driver (probably because the driver is smart enough to recognize that the query can't be run as a prepared statement) and never even makes it to SQL Server.

Bottom line - it looks like you cannot use <cfqueryparam> with temporary tables due to SQL Server's restrictions.

-Carl V.