Dynamic Sorting within SQL Stored Procedures

Yeah, it's a pain, and the way you're doing it looks similar to what I do:

order bycase when @SortExpr = 'CustomerName' and @SortDir = 'ASC'     then CustomerName end asc, case when @SortExpr = 'CustomerName' and @SortDir = 'DESC'     then CustomerName end desc,...

This, to me, is still much better than building dynamic SQL from code, which turns into a scalability and maintenance nightmare for DBAs.

What I do from code is refactor the paging and sorting so I at least don't have a lot of repetition there with populating values for @SortExpr and @SortDir.

As far as the SQL is concerned, keep the design and formatting the same between different stored procedures, so it's at least neat and recognizable when you go in to make changes.

This approach keeps the sortable columns from being duplicated twice in the order by, and is a little more readable IMO:

SELECT  s.*FROM  (SELECT    CASE @SortCol1      WHEN 'Foo' THEN t.Foo      WHEN 'Bar' THEN t.Bar      ELSE null    END as SortCol1,    CASE @SortCol2      WHEN 'Foo' THEN t.Foo      WHEN 'Bar' THEN t.Bar      ELSE null    END as SortCol2,    t.*  FROM    MyTable t) as sORDER BY  CASE WHEN @dir1 = 'ASC'  THEN SortCol1 END ASC,  CASE WHEN @dir1 = 'DESC' THEN SortCol1 END DESC,  CASE WHEN @dir2 = 'ASC'  THEN SortCol2 END ASC,  CASE WHEN @dir2 = 'DESC' THEN SortCol2 END DESC

Dynamic SQL is still an option. You just have to decide whether that option is more palatable than what you currently have.

Here is an article that shows that: http://www.4guysfromrolla.com/webtech/010704-1.shtml.