Dynamic ORDER BY Clauses for ASP.NET Server-Side Paging with Stored Procedures

My "compulsive obsession" to bring SQL Server 2005 stored procedures to the user-experience equivalent of dynamic T-SQL queries with a GridView bound to a LinqDataSource—regardless of the development effort required—continues.

When hydrating LINQ to SQL entities, I finally got server-side (incremental) paging to work properly with a LinqDataSource, bound DataGrid view and a stored procedure derived from a sample query. (See the introduction and "Attempts to Fix the Server-Side Paging Problems" sections of Problems Using Stored Procedures for LINQ to SQL Data Retrieval from 9/8/2007, updated 9/17/2007.) Here's a screen capture of the Orders test harness displaying 10 of the 122 Northwind Orders from customers in the USA:

Click capture to display full-size image in another window.

It was obvious that ad-hoc sorting wasn't behaving as expected for properties other than OrderID. For example, the preceding screen capture shows a descending sort on the Customer.CompanyName property. The sort's scope is the 10 rows displayed; the GridView sorts the rows. The same is true for ad-hoc sorts on all other properties except OrderID.

A quick review of a typical dynamic T-SQL query for page 2 with the simplest case, an ascending sort on the Freight property, showed the syntax required to increase the scope of the sort to all entities in the set:

exec sp_executesql N'SELECT TOP 10 [t2].[OrderID], [t2].[CustomerID],
    [t2].[EmployeeID], [t2].[OrderDate], [t2].[RequiredDate],
    [t2].[ShippedDate], [t2].[ShipVia], [t2].[Freight], [t2].[ShipName],
    [t2].[ShipAddress], [t2].[ShipCity], [t2].[ShipRegion],
    [t2].[ShipPostalCode], [t2].[ShipCountry]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY [t0].[Freight], [t0].[OrderID]
        DESC) AS [ROW_NUMBER], [t0].[OrderID], [t0].[CustomerID],
        [t0].[EmployeeID], [t0].[OrderDate], [t0].[RequiredDate],
        [t0].[ShippedDate], [t0].[ShipVia], [t0].[Freight], [t0].[ShipName],
        [t0].[ShipAddress], [t0].[ShipCity], [t0].[ShipRegion], 
        [t0].[ShipPostalCode], [t0].[ShipCountry]
    FROM [dbo].[Orders] AS [t0]
    LEFT OUTER JOIN [dbo].[Customers] AS [t1]
        ON [t1].[CustomerID] = [t0].[CustomerID]
    WHERE [t1].[Country] = @p0
    ) AS [t2]
WHERE [t2].[ROW_NUMBER] > @p1
ORDER BY [t2].[Freight], [t2].[OrderID] DESC',N'@p0 nvarchar(3),
    @p1 int',@p0=N'USA',@p1=10

The red ORDER BY expression for the ROW_NUMBER() function shows the syntax for creating the rowset from which page 2 (or greater) is obtained. Notice that both Freight and OrderID fields are included in the sort expression. The green expression is a holdover from the page 1 code and isn't required by the preceding example.

It's a better approach to use an explicit query than run sp_executesql to conditionally execute string-based queries from within the stored proc because of possible problems with caching the query execution plan and the brittleness of unchecked query syntax. 

Garth Wells' demonstrated how to write CAST expressions to customize ORDER BY expressions in his 2001 Dynamic ORDER BY article; a fix by "Wildthing" in the comments thread accommodates different datatypes. Here's the stored proc code to handle various single-parameter sorts:

CREATE PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSorted(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
    @SortCol nvarchar(128) = 'OrderID',
    @SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE -- Numeric types ascending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
                THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
                THEN t0.Freight
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
                THEN t0.EmployeeID
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
                THEN t0.ShipVia
        END ASC,
        CASE -- Numeric types descending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
               THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
               THEN t0.Freight
        END DESC,
        CASE -- Character types ascending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
                THEN t0.CustomerID
        END ASC,
        CASE -- Character types descending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
                THEN t0.CustomerID
        END DESC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
                THEN t0.ShippedDate
        END ASC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
                THEN t0.ShippedDate
        END DESC
        ) AS ROW_NUMBER, t0.OrderID,
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS t1 ON t1.CustomerID = t0.CustomerID
    WHERE Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's a screen capture of a later version of the test harness that sorts all columns with the preceding stored procedure. The ORDER BY clause is CustomerID DESC, OrderID DESC with many instances of ASC, ASC, ASC, ... DESC, DESC, DESC, which the query parser appears to ignore.

Click capture to display full-size image in another window.

The basic CAST approach gives the same sort sequence for CustomerID vs. Customer.CompanyName, but different sequences for EmployeeID vs. Employee.LastName and ShipVia vs. Shipper.CompanyName. Here's the modified stored proc code (red) to sort the entire data set on EntityRef values:

ALTER PROCEDURE dbo.usp_GetOrdersByCountryPagedAndSortedExt(
    @Country nvarchar(15) = 'USA',
    @MaximumRows int = 20,
    @StartRowIndex int = 0,
    @SortCol nvarchar(128) = 'OrderID',
    @SortDir nvarchar(4) = 'DESC')
AS
SET NOCOUNT ON
SELECT TOP(@MaximumRows) t2.OrderID, t2.CustomerID, t2.EmployeeID,
    t2.OrderDate,     t2.RequiredDate, t2.ShippedDate, t2.ShipVia,
    t2.Freight, t2.ShipName, t2.ShipAddress, t2.ShipCity, t2.ShipRegion,
    t2.ShipPostalCode, t2.ShipCountry
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY
        CASE -- Numeric types ascending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'ASC'
                THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'ASC'
                THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'ASC'
                THEN t0.Freight
        END ASC,
        CASE -- Numeric types descending
            WHEN @SortCol = 'OrderID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'EmployeeID' AND @SortDir = 'DESC'
               THEN t0.OrderID
            WHEN @SortCol = 'ShipperID' AND @SortDir = 'DESC'
               THEN t0.OrderID   
            WHEN @SortCol = 'Freight' AND @SortDir = 'DESC'
               THEN t0.Freight
        END DESC,
        CASE -- Character types ascending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'ASC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'ASC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'ASC'
                THEN Customer.CompanyName
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'ASC'
                THEN Employee.LastName
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'ASC'
                THEN Shipper.CompanyName
        END ASC,
        CASE -- Character types descending
            WHEN @SortCol = 'CustomerID' AND @SortDir = 'DESC'
                THEN t0.CustomerID
            WHEN @SortCol = 'ShipName' AND @SortDir = 'DESC'
                THEN t0.ShipName
            WHEN @SortCol = 'Customer.CompanyName' AND @SortDir = 'DESC'
                THEN Customer.CompanyName
            WHEN @SortCol = 'Employee.LastName' AND @SortDir = 'DESC'
                THEN Employee.LastName
            WHEN @SortCol = 'Shipper.CompanyName' AND @SortDir = 'DESC'
                THEN Shipper.CompanyName
        END DESC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'ASC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'ASC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'ASC'
                THEN t0.ShippedDate
        END ASC,
        CASE -- Date types ascending
            WHEN @SortCol = 'OrderDate' AND @SortDir = 'DESC'
                THEN t0.OrderDate
            WHEN @SortCol = 'RequiredDate' AND @SortDir = 'DESC'
                THEN t0.RequiredDate
            WHEN @SortCol = 'ShippedDate' AND @SortDir = 'DESC'
                THEN t0.ShippedDate
        END DESC
        ) AS ROW_NUMBER, t0.OrderID,
        t0.CustomerID, t0.EmployeeID, t0.OrderDate, t0.RequiredDate,
        t0.ShippedDate, t0.ShipVia, t0.Freight, t0.ShipName, t0.ShipAddress,
        t0.ShipCity, t0.ShipRegion, t0.ShipPostalCode, t0.ShipCountry
    FROM dbo.Orders AS t0
    LEFT OUTER JOIN dbo.Customers AS Customer
        ON Customer.CustomerID = t0.CustomerID
    LEFT OUTER JOIN dbo.Employees AS Employee
        ON Employee.EmployeeID = t0.EmployeeID
    LEFT OUTER JOIN dbo.Shippers AS Shipper
        ON Shipper.ShipperID = t0.ShipVia
    WHERE Customer.Country = @Country
    ) AS t2
WHERE t2.ROW_NUMBER > @StartRowIndex
SET NOCOUNT OFF

Here's the test harness with the Employee column sorted ascending and an Ext. check box to select the extended sort capability:

Click capture to display full-size image in another window.

The preceding code doesn't attempt to sort multiple columns. It might be tempting to write a stored proc for two or three successive sort sequences with EXEC or sp_executesql and dynamic T-SQL. However, LINQ to SQL won't codegen function calls for stored procs that execute dynamic SQL. Also considering the security and query plan issues, it probably makes more sense to pass two or three pairs of @SortCol/@SortDir values and stack a set of CASE statements for each.

Update 9/19/2007: Minor edits and typos corrected .

Technorati Tags: LINQ to SQL, Entity Data Model Sets, Entity Refs, LINQ, LinqDataSource, Sorting Grids, SQL Server 2005, Stored Procedures, T-SQL, CAST