Running multiple queries inside one cfquery code block

Question

DB: MSSQL 2008
CF Version: 9

<cfquery name="rsData" datasource="#request.dsn#">
SELECT GETDATE() AS CurrentDateTime1
SELECT GETDATE() AS CurrentDateTime2
</cfquery>
<cfoutput query="rsData">  
    #rsData.CurrentDateTime1#
    #rsData.CurrentDateTime2#
</cfoutput>
<cfdump var="#rsData#">

I would expect to be able to output #rsData.CurrentDateTime2# as this is written but I get a hard error:

Element CURRENTDATETIME2 is undefined in rsData

Any ideas as to why? Is there a CFAdmin setting that needs to be enabled? There are other areas of the code where this works just fine so I'm a bit perplexed. Any suggestions on where to look to resolve this would be appreciated.

** UPDATED BASED UPON LEIGH'S ANSWER MAKING ME GO BACK AND SEE MY TYPO ** This is just a dirty example of what I was ultimately trying to do and may not work.

declare @start  DATETIME, @end DATETIME
    set @start = (
        select max(date) DTM
        from TABLE)
        SET @lowStart_Search = dateadd(year,-1,@highStart_Search)

        SELECT COLUMN1, COLUMN2  
        WHERE column3 <= @start  
        AND   column3 >= @end

This article about setting "allowMultiQueries=true" in the admin might be relevant to your situation but I've never actually used it. bennadel.com/blog/… — Tony Junkes
– Tony Junkes, Commented Oct 5, 2015 at 15:57
Why do you need multiple queries to run this? Why not just SELECT getDate() AS date1, getDate() AS date2 — Matt Busche
– Matt Busche, Commented Oct 5, 2015 at 16:00
@tonyjunkes - from my research, that only applies to MySQL unless you know otherwise. — HPWD
– HPWD, Commented Oct 5, 2015 at 16:43
@MattBusche the getDate() were just examples of a select statement. In the code, they are performing lookups. — HPWD
– HPWD, Commented Oct 5, 2015 at 16:45
to others reading this, the question above has been updated to include resemblance of the corrected code. The issue was I had two selects instead of a SET and a SELECT — HPWD
– HPWD, Commented Oct 5, 2015 at 17:03

Leigh · Accepted Answer · 2015-10-05 16:46:35Z

1

There are other areas of the code where this works just fine

That seems unlikely. There must be something different about the other queries.

It is not about multiple SQL statements per se, but how many resultsets those statements generate. CFQuery is only capable of returning a single resultset. The SQL above generates multiple resultsets:

Resultset 1: SELECT GETDATE() AS CurrentDateTime1
Resultset 2: SELECT GETDATE() AS CurrentDateTime2

Notice if you run it in SSMS it produces two result grids, not one?

First Result

CurrentDateTime1
------------------
October, 05 2015 16:36:31
(1 row affected)

Second Result

CurrentDateTime2
------------------
October, 05 2015 16:36:31
(1 row affected)

The CFQuery tag will only return the first result. The rest will be ignored. Hence why CurrentDateTime2 is undefined.

You can certainly modify the SQL to return both values, if needed. For example, return them as two separate columns of a single resultset.

 SELECT GETDATE() AS CurrentDateTime1
      , GETDATE() AS CurrentDateTime2

There are other options as well, depending on the ultimate goal (which is not clear from the simplified example). However, again a cfquery will only ever return a single result. If you actually do need multiple resultsets, you must use cfstoredproc instead.

edited Oct 5, 2015 at 16:46

answered Oct 5, 2015 at 16:20

Leigh

28.9k10 gold badges58 silver badges109 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

HPWD Over a year ago

Oh good grief! @leigh Your single comment "That seems unlikely. A cfquery is only capable of returning a single resultset" made me look at the queries again as I KNEW this be true and wouldn't you know it, in a hurried rush, I had a typo. Had I slowed down just a bit, I would have seen my error. The first select statement should have been a simple set statement.

HPWD Over a year ago

I'm marking this as correct and updating adding a comment to my post since your post got me to the right answer.

Leigh Over a year ago

Heh, hate it when that happens! (I figured the additional descriptions might help the next guy or someone else not as familiar with the details about the cfquery tag).

Leigh Over a year ago

@HPWD - BTW, feel free to post the fixed sql as a separate "answer, if you think that would be helpful to others.

Collectives™ on Stack Overflow

Running multiple queries inside one cfquery code block

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related