Dynamic Sorting within SQL Stored Procedures

Answer 1

How about handling sorting on the stuff displaying the results -- grids, reports, etc. rather than on SQL?

EDIT:

To clarify things since this answer got down-voted earlier, I'll elaborate a bit...

You stated you knew about client-side sorting but wanted to steer clear of it. That's your call, of course.

What I want to point out, though, is that by doing it on the client-side, you're able to pull data ONCE and then work with it however you want -- versus doing multiple trips back and forth to the server each time the sort gets changed.

Your SQL Server isn't getting taxed right now and that's awesome. It shouldn't be. But just because it isn't overloaded yet doesn't mean that it'll stay like that forever.

If you're using any of the newer ASP.NET stuff for displaying on the web, a lot of that stuff is already baked right in.

Is it worth adding so much code to each stored procedure just to handle sorting? Again, your call.

I'm not the one who will ultimately be in charge of supporting it. But give some thought to what will be involved as columns are added/removed within the various datasets used by the stored procedures (requiring modifications to the CASE statements) or when suddenly instead of sorting by two columns, the user decides they need three -- requiring you to now update every one of your stored procedures that uses this method.

For me, it's worth it to get a working client-side solution and apply it to the handful of user-facing displays of data and be done with it. If a new column is added, it's already handled. If the user wants to sort by multiple columns, they can sort by two or twenty of them.

Answer 2

I feel your pain. Unfortunately, no, I don't have a better way.

Answer 3

I have the same issue. Never found a way around it.

Answer 4

At some point, doesn't it become worth it to move away from stored procedures and just use parameterized queries to avoid this sort of hackery?

Answer 5

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.

Answer 6

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.

Answer 7

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.)

Answer 8

I agree, use client side. But it appears that is not the answer you want to hear.

So, it is perfect the way it is. I don't know why you would want to change it, or even ask "Is there a better way." Really, it should be called "The Way". Besides, it seems to work and suit the needs of the project just fine and will probably be extensible enough for years to come. Since your databases aren't taxed and sorting is really really easy it should stay that way for years to come.

I wouldn't sweat it.

Answer 9

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.

Answer 10

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

Answer 11

This solution might only work in .NET, I don't know.

I fetch the data into the C# with the initial sort order in the SQL order by clause, put that data in a DataView, cache it in a Session variable, and use it to build a page.

When the user clicks on a column heading to sort (or page, or filter), I don't go back to the database. Instead, I go back to my cached DataView and set its "Sort" property to an expression I build dynamically, just like I would dynamic SQL. ( I do the filtering the same way, using the "RowFilter" property).

You can see/feel it working in a demo of my app, BugTracker.NET, at http://ifdefined.com/btnet/bugs.aspx

Answer 12

You should avoid the SQL Server sorting, unless if necessary. Why not sort on app server or client side? Also .NET Generics does exceptional sortin

Answer 13

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

Answer 14

I know this is an old thread, but I just wanted to say 'thanks' for discussing this.

I've been plagued with this issue on a particular project from the time I was brought on. All the select stored procedures (which were designed by someone fired immediately before I started working on the project) had custom paging built-in. This was not so much of a problem except the client requested that we also add sorting to most of the GridViews. Sounded simple enough as I knew the gridview had sorting built-in. Once I got my hands into though, I realized that using custom paging in your stored procedures is incompatible with the GridView's built-in sorting. The best you can do is sort the records displayed on the page (mostly useless, and certainly unexpected behavior for the user).

In a time-crunch and hopelessly naive as I was, I just added a @SortBy varchar parameter to the necessary SPs and implemented the EXEC solution to this problem, leaving my DAL to compose the ORDER BY clause in the correct format. I knew at the time that there must be a better way, and I thought about using CASE statements in the ORDER BY clause, but figured it would take too long (they needed to be able to sort on at least 4 columns at a time) and be so much more complex. But I went against the better coding angel on my shoulder and went ahead with the EXEC statement, and now I'm paying for it trying to edit and debug the select statements in the EXEC.

I just wish there was a better way. Could SQL server not have a dbo data type that can be used in this manner?

Answer 15

When you are paging sorted results, dynamic SQL is a good option. If you're paranoid about SQL injection you can use the column numbers instead of the column name. I've done this before using negative values for descending. Something like this...

declare @o int;
set @o = -1;

declare @sql nvarchar(2000);
set @sql = N'select * from table order by ' + 
    cast(abs(@o) as varchar) + case when @o < 0 then ' desc' else ' asc' end + ';'

exec sp_executesql @sql

Then you just need to make sure the number is inside 1 to # of columns. You could even expand this to a list of column numbers and parse that into a table of ints using a function like this. Then you would build the order by clause like so...

declare @cols varchar(100);
set @cols = '1 -2 3 6';

declare @order_by varchar(200)

select @order_by = isnull(@order_by + ', ', '') + 
        cast(abs(number) as varchar) + 
        case when number < 0 then ' desc' else '' end
from dbo.iter_intlist_to_tbl(@cols) order by listpos

print @order_by

One drawback is you have to remember the order of each column on the client side. Especially, when you don't display all the columns or you display them in a different order. When the client wants to sort, you map the column names to the column order and generate the list of ints.

Answer 16

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.