I wrote a gateway function at work a couple of weeks ago that aggregates a bunch of data, then returns a paged query. The user interface for this functionality shows a set of contexts, each of which can have an indefinite number of sub-records. Each of the contexts that match the search criteria needs to be numbered, because if there are many of them, we need to paginate the results, and give back contexts 1 -25, then 26 – 50, and so on.

My first pass at a solution involved a CTE (Common Table Expression), where you define a subtable and query that, and this was a great improvement over the initial straight query. Unfortunately, it wasn’t enough of an improvement… the next day I noticed that the query was taking about 14 seconds to run.

I ended up using a temporary table for the method. This was only the second time I’d used a temporary table, and the first time I used one to return results; the first time was used simply to mass insert data.

In the method, I create a unique name for the temporary table, then in the SQL, define the table. It’s important to note that when defining a locally scoped table you need to prefix the name with a pound sign, and since this is ColdFusion, you need to double up the pound sign.

So I created the table, and inserted the data into it from my base query. One of the nice side benefits of using a temporary table was that I was able to simply create a primary key with identity for the context number, which simplified things a bit. I then selected from the temporary table, joining to one of our standing tables to fill out the rest of the data. After getting the data, the query drops the temporary table.

[Queryname] is undefined

This worked out great in SQL Studio; it was much faster than the CTE approach– 300ms as opposed to 14 seconds, so I got the go ahead to replace the existing query with the new version. I replaced the old SQL with the new, and — crash. I got that lovely grey and blue screen of death, stating that “GetData [the query name] was undefined. Whaaaat?!?!? It’s right there in front of me!

Solution

I did some Googling, and found the key. I added SET NOCOUNT ON before the rest of the SQL, and SET NOCOUNT OFF at the end, and ColdFusion was happy. In hindsight, it may be because either I was performing multiple operations in the one query, or possibly because the SELECT was not the last operation, because I had to drop the temporary table after I was finished with it.