Ordering data using dynamic SQL versus temporary tables

Thursday, 1 May 2008

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

Wednesday, 30 April 2008

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

Tuesday, 29 April 2008

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:

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.

Light reading update

Sunday, 27 April 2008

Last month I ordered a few books to add to my "programmers" library.

A few days later, I added an extra title to my order:

Because MCPD 70-547 Exam Prep: Microsoft Certified Web Application Developer Exam (by Amit Kalani) was supposed to be published by 30 March, my order was held over, however when 1st April came around and it still wasn't available, Amazon kindly split my order and sent the first three books.

It still wasn't available a few weeks later, so they shipped the Facts and Fallacies book and gave me the option of cancelling my order for the remaining book.

Seeing as I have an exam voucher that expires June 30, I'd like to get some study material before that date to give me a good chance of passing. So I did cancel the Kalani book and I've gone with the Microsoft Press book instead - MCPD Self-Paced Training Kit (Exam 70-547): Designing and Developing Web-Based Applications Using the Microsoft .NET Framework.

Even if not all the reviews of this book are glowing, at least it has actually been published!

Coming soon - my reviews of these books....

Software on my desktop

Saturday, 26 April 2008

Following on from my list of Firefox extensions, here's a list of the applications I've been using on my work PC: