Thursday, 1 May 2008

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".


David said...

As you say, "it depends!"

From a maintenance point of view, the temporary table is the only way to go. There is no safe way to maintain an SQL query (or any code) that has to be maintained in two places, or inside a string variable.

I don't know of the performance differences, but cpu cycles are cheaper than developer sanity.

Of course, this is a trick question! Surely you would be retrieving data into an object with it's own ordering methods.

David Gardiner said...

Certainly the maintenance aspect was weighing on my mind.

One other variation to throw in the mix is to make use of a table-value function to encapsulate the main query and then using that with the dynamic SQL technique. This means more of the SQL is not hidden as a string.

Jason Stangroome said...

Hi David,

As another approach, in the past I've handled the same situation like this:

FROM Table1
CASE WHEN @order = 'Column1'
THEN Column1
ELSE Column2 END


David Gardiner said...

Hey, that is cool! I'm not sure I knew you could use a CASE there.

Thanks Jason :-)