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
Default

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.

Example:
Code:
CREATE PROC foo 
    @pagenumber int,
    @pagesize int,
    @orderby varchar(20)
AS
-- 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
Code:
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.