View Single Post
  #10 (permalink)  
Old July 28th, 2008, 02:12 AM
Old Pedant Old Pedant is offline
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Lee Dumond wrote:
Write SQL that implements a dynamic query for sorting.

Excepting that in most situations you don't REALLY need a dynamic query for sorting.

You can usually get away with a parameterized ORDER BY.

    @pagenumber int,
    @pagesize int,
    @orderby varchar(20)
-- I'm going to ignore the pagenumber & pagesize for this demo
-- In "real life" I'd use them as the 4GuysFromRolla article shows

SELECT name, age, salary FROM table
ORDER BY ( CASE WHEN @ordering = 'name'   THEN name   ELSE '' END ),
         ( CASE WHEN @ordering = 'age'    THEN age    ELSE 0  END ),
         ( CASE WHEN @ordering = 'salary' THEN salary ELSE 0.0 END )

You just have to be careful that the ELSE for each of the possible ordering fields matches the datatype of that field.

So now, if (say) the @ordering is 'age', then that query becomes the equivalent of
SELECT name, age, salary FROM table
ORDER BY '', age, 0.0
And so only the age column enters into the ordering.

And if you are *TRULY* concerned about performance, you'd just use IF ... ELSE IF .... ELSE IF ... in the T-SQL and clone the query code except for the ORDER BY. Tedious SP writing, but mostly simple cut & paste, so why not? Probably cleaner than using dynamic sql in the SP.