Skip to main content
S
stevehn
Participant
April 21, 2017
Answered

Insert into SQL Temp table with cfqueryparam

  • April 21, 2017
  • 1 reply
  • 1384 views

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

    This topic has been closed for replies.
    Correct answer Carl Von Stetten

    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.

    1 reply

    Carl Von Stetten
    Carl Von StettenCorrect answer
    Legend
    April 22, 2017

    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.

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices