Sorting the result of a stored procedure

Question

I'd like to sort on a column in the result of a stored procedure without having to add the Order By clause in the stored procedure. I don't want the data to be sorted after I have executed the query, sorting should be part of the query if possible. I have the following code:

        public static DataTable RunReport(ReportQuery query)
        {
            OffertaDataContext db = new OffertaDataContext();
            Report report = (from r in db.Reports where r.Id == (int)query.ReportId select r).Single(); 
            //???: check security clearance.

            DataSet dataSet = new DataSet();

            /*
            doesn't work, I guess the "Result" table hasn't been created yet;
            if(!string.IsNullOrEmpty(query.SortField))
            {
                dataSet.DefaultViewManager.DataViewSettings["Result"].Sort = query.SortField + " " + (query.SortAscending ? "ASC" : "DESC"); 
            }
            */

            using (SqlConnection conn = new SqlConnection(Config.ConnectionString))
            {
                conn.Open();
                using (SqlCommand exec = conn.CreateCommand())
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter())
                    {
                        exec.Connection = conn;
                        exec.CommandType = CommandType.StoredProcedure;
                        exec.CommandText = report.ReportProc;

                        adapter.SelectCommand = exec;
                        try
                        {

                            adapter.Fill(dataSet, query.Skip, query.Take, "Result");
                        }
                        catch (Exception e)
                        {
                            throw e;
                        }
                        finally
                        {
                            conn.Close();
                        }
                        return dataSet.Tables["Result"];
                    }
                }
            }
        }

How do I add sorting?

Thanks! /Niels

Answer 1

To be honest, since you are using DataTable, you might as well just sort at the client.

Dynamic sorting (at the server) via SPs etc is always a pain; to do it in pure TSQL, you either need some horribly inefficient CASE block at the end of the SELECT, or you need to use dynamic SQL (for example via sp_ExecuteSQL), manipulating the ORDER BY in the final query. The only other option (in raw TSQL) would be to EXEC/INTO to get the data into a table variable (or temp table), then SELECT from this with an ORDER BY.

If it is an option, LINQ-to-SQL actually does OK at this; it supports querying (and composing against) UDFs - so rather than an SP, code the query in a UDF (the SP can always just SELECT from the UDF if you need to support legacy callers). Then you can use "order by" etc in a LINQ query:

var qry = from row in ctx.SomeMethod(args)
          order by row.Name, row.Key
          select row;

(or there are various methods for adding a dynamic sort to a LINQ query - the above is just a simple example)

the final TSQL will be something like:

SELECT blah FROM theudf(args) ORDER BY blah

i.e. it will get it right, and do the "ORDER BY" at the server. This is particularly useful when used with Skip() and Take() to get paged data.

Answer 2

Get the DataTable you are populating in the dataSet ("Result").

Now - there's no way to sort the DataTable, except via the Query, View, or Stored Procedure that populates it.

Since you don't wanna do it in the SP, you can sort the DefaultView of the DataTable, or any DataView that is associated with the DataTable.

You can achieve it using the Sort property of the DataView. This is a string which specifies the column (or columns) to sort on, and the order (ASC or DESC).

Example:

myTable.DefaultView.Sort = "myColumn DESC";

You can now use the DefaultView to do whatever you want (bind it to something or whatever)