Dynamic Sorting within SQL Stored Procedures

Question

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

order by
case 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 s
ORDER 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.

My applications do this a lot but they are all dynamically building the SQL. However, when I deal with stored procedures I do this:

1. Make the stored procedure a function that returns a table of your values - no sort.
2. Then in your application code do a select * from dbo.fn_myData() where ... order by ... so you can dynamically specify the sort order there.

Then at least the dynamic part is in your application, but the database is still doing the heavy lifting.

A stored procedure technique (hack?) I've used to avoid dynamic SQL for certain jobs is to have a unique sort column. I.e.,

SELECT
   name_last,
   name_first,
   CASE @sortCol WHEN 'name_last' THEN [name_last] ELSE 0 END as mySort
FROM
   table
ORDER BY 
    mySort

This one is easy to beat into submission -- you can concat fields in your mySort column, reverse the order with math or date functions, etc.

Preferably though, I use my asp.net gridviews or other objects with build-in sorting to do the sorting for me AFTER retrieving the data fro Sql-Server. Or even if it's not built-in -- e.g., datatables, etc. in asp.net.

There's a couple of different ways you can hack this in.

Prerequisites:

1. Only one SELECT statement in the sp
2. Leave out any sorting (or have a default)

Then insert into a temp table:

create table #temp ( your columns )

insert #temp
exec foobar

select * from #temp order by whatever

Method #2: set up a linked server back to itself, then select from this using openquery: http://www.sommarskog.se/share_data.html#OPENQUERY

There may be a third option, since your server has lots of spare cycles - use a helper procedure to do the sorting via a temporary table. Something like

create procedure uspCallAndSort
(
    @sql varchar(2048),        --exec dbo.uspSomeProcedure arg1,'arg2',etc.
    @sortClause varchar(512)    --comma-delimited field list
)
AS
insert into #tmp EXEC(@sql)
declare @msql varchar(3000)
set @msql = 'select * from #tmp order by ' + @sortClause
EXEC(@msql)
drop table #tmp
GO

Caveat: I haven't tested this, but it "should" work in SQL Server 2005 (which will create a temporary table from a result set without specifying the columns in advance.)