Ordering data using dynamic SQL versus temporary tables

Here’s a scenario that’s not that uncommon:

You have a query and the user wants to be able to choose at run-time which column to sort/group by.

The problem is you can’t use a variable to indicate the column name in the ORDER BY or GROUP BY clauses.

If the query itself is very simple, then you could just repeat the query with specified ordering using IF. eg.

IF (@order = ‘Column1’)

SELECT * from Table1 ORDER BY Column1

ELSE IF (@order = ‘Column2’)

SELECT * from Table1 ORDER BY Column2

If the query is non-trivial, you probably wouldn’t want to use this however, as maintenance becomes problematic.

There are two obvious solutions to this:

Dynamic SQL

Assemble the query into a varchar variable and then execute it using sp_executesql.

DECLARE @sql varchar(4000)

SET @sql = ‘SELECT * FROM Table1’

IF (@order = ‘Column1’)

SET @sql = @sql + ' ORDER BY Column1'

ELSE IF (@order = ‘Column2’)

SET @sql = @sql + ' ORDER BY Column2'

sp_executesql @sql

Temporary Tables

Split the query into two parts. First of all, retrieve the data into a temporary table (this is one of the few times you can’t use a table variable). Next, use the original IF .. ELSE IF method to return the data from the temporary table.

SELECT * INTO #Stuff FROM Table1

IF (@order = ‘Column1’)

SELECT \* FROM #Stuff ORDER BY Column1

ELSE IF (@order = ‘Column2’)

SELECT \* FROM #Stuff ORDER BY Column2

So which is better? Great question, and I’m not really sure of the answer. I suspect it could well be “it depends”.

Copy Full Path for Windows XP

I’m missing the “Copy Full Path” feature from Windows Explorer in Vista now that I’m back in WinXP land. And that’s where Copy Path to Clipboard comes into play.

DECS, Day 1

Yesterday was my final day at UniSA - spent out at Mawson Lakes doing handover with Damien, Brad and Richard. I also caught up with Brook, who’s a bit of a shibboleth expert amongst other things.

Today was my first day at the Department of Education and Children’s Services (or DECS for short). I guess it’s pretty normal to make comparisons, but here’s what I’ve noticed so far:

• Nice, friendly people (though it is different working on a floor with heaps of other people, compared to a small team).
• Good induction experience.
• Lots of documentation and things to learn/remember.
• Weird using WinXP with 1Gb ram and a single small monitor. Most other people have dual monitors and 2Gb, so I might ask if there’s any chance I can get a hardware refresh.
• My login and email were all ready and waiting for me (that is impressive) which meant I could get started straight away.
• There’s no tea or coffee or milk - everyone brings their own! Might be easier (and healthier) to stick with water.
• I do have Admin access to my PC (wasn’t sure if I would get that)
• The image they refreshed my PC with must be a couple of years old, as a fair bit of my time was spent installing patches and updates.
• Visual InterDev and Visual Studio 2003 - Not something I ever thought I’d be using again, but there you go. They’re talking about Visual Studio 2008 coming soon - not soon enough I say!
• Sounds like some people do ride to work (and there is a shower), but I’m not sure if there’s any lockers.
• I’ll have to figure out what time the tram goes past to get to the train station on time.
• They’re using SQL 2000 with Reporting Services, so I’ll be sure to give the Adelaide SQL User Group talks a plug. Not that I think they’ll be moving to SQL 2008 anytime soon, but it’s good to know what’s possible.
• Interesting experience with Internet access. There’s no obvious proxy server, but some sites are blocked (Facebook is one example). They’re also not too keen on IM by the looks of things.
• One of my colleagues mentioned to me that our manager is probably the best he’s worked with. That’s encouraging to hear.

So it was a pretty full on day. Certainly a huge change from what I was used to, but I think it’s going to work out ok. And as I said before, the people seem really nice, which helps a lot too.