Comments on David Gardiner - Dave's Daydreams: Ordering data using dynamic SQL versus temporary t...

Hey, that is cool! I'm not sure I knew you could u...

2008-05-01T16:00:00.000+09:30

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

Thanks Jason :-)

Hi David,

As another approach, in the past ...

2008-05-01T14:45:00.000+09:30

Hi David,

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

SELECT
*
FROM Table1
ORDER BY
CASE WHEN @order = 'Column1'
THEN Column1
ELSE Column2 END

Regards,

Certainly the maintenance aspect was weighing on m...

2008-05-01T14:06:00.000+09:30

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.

As you say, "it depends!"

From a maintenanc...

2008-05-01T11:49:00.000+09:30

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.

Comments on David Gardiner - Dave's Daydreams: Ordering data using dynamic SQL versus temporary t...

Hey, that is cool! I'm not sure I knew you could u...

Hi David,As another approach, in the past ...

Certainly the maintenance aspect was weighing on m...

As you say, "it depends!"From a maintenanc...

Hi David,

As another approach, in the past ...

As you say, "it depends!"

From a maintenanc...