Broken query using sorting by different columns using ROW_NUMBER () OVER () in SQL Server 2005

Question

Suppose I use a Northwind database, and I would like to run a query through a stored procedure that contains, among other parameters, the following:

• @Offset to indicate where pagination begins,
• @Limit to indicate page size,
• @SortColumn to indicate the column used for sorting,
• @SortDirection to indicate sorting by ascendant or descendants.

The idea is to do pagination in the database, since the result set contains thousands of rows, so caching is not an option (and using VIEWSTATE is not even considered, IMO, sucks).

As you know, SQL Server 2005 provides the ROW_NUMBER function, which returns the row number in a section of a result set, starting at 1 for the first row in each section.

We need to sort by each returned column (five in this example), and dynamic SQL is not an option, so we have two options: using a large number of IF ... ELSE ... and having 10 queries that damn it support , or the request is as follows:

 WITH PaginatedOrders AS ( SELECT CASE (@SortColumn + ':' + @SortDirection) WHEN 'OrderID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC) WHEN 'OrderID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC) WHEN 'CustomerID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID ASC) WHEN 'CustomerID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.CustomerID DESC) WHEN 'EmployeeID:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID ASC) WHEN 'EmployeeID:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.EmployeeID DESC) WHEN 'OrderDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate ASC) WHEN 'OrderDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderDate DESC) WHEN 'ShippedDate:A' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID ASC) WHEN 'ShippedDate:D' THEN ROW_NUMBER() OVER (ORDER BY Orders.OrderID DESC) END AS RowNumber, OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate FROM Orders -- WHERE clause goes here ) SELECT RowNumber, OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate, @Offset, @Limit, @SortColumn, @SortDirection FROM PaginatedOrders WHERE RowNumber BETWEEN @Offset AND (@Offset + @Limit - 1) ORDER BY RowNumber 

I tried the request several times, with different arguments, and its performance is actually quite good, but it still looks like it could be optimized in some other way.

Is there something wrong with this question, or will you do it like that? Do you suggest a different approach?

Answer 1

Plain:

 SELECT OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate, @Offset, @Limit, @SortColumn, @SortDirection FROM Orders WHERE ROW_NUMBER() OVER ( ORDER BY /* same expression as in the ORDER BY of the whole query */ ) BETWEEN (@PageNum - 1) * @PageSize + 1 AND @PageNum * @PageSize /* AND more conditions ... */ ORDER BY CASE WHEN @SortDirection = 'A' THEN CASE @SortColumn WHEN 'OrderID' THEN OrderID WHEN 'CustomerID' THEN CustomerID /* more... */ END END, CASE WHEN @SortDirection = 'D' THEN CASE @SortColumn WHEN 'OrderID' THEN OrderID WHEN 'CustomerID' THEN CustomerID /* more... */ END END DESC 

This will sort by NULL (DESC) if the ASC order is selected or vice versa.

Let the ROW_NUMBER () function work on the same ORDER BY clause.