title: Stored Procedure and Paging with Ordering and filtering
body: <p>I have data in a table and written a nice stored procedure which has a CTE and does paging wonderfully.</p>
  
  <p>I now have a request to be able to order by a given column name and in a particular direction (ASC/DESC) - not so much of a problem but it seems to perform weird ordering/sorting just for that page in that it only sorts it for that result set to be returned back rather than having the current page in the correct order of sorting.</p>
  
  <p>Here is the stored procedure code:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[sp_GetProducts] (
      @itemsPerPage int,
      @pageNumber int,
      @totalRecords int OUTPUT,   
      @sortByFieldName nvarchar(30) = 'ID',
      @sortDir nvarchar(5) = 'ASC'
  )
  AS
  BEGIN
      SET NOCOUNT ON;
  
      DECLARE @firstRow int
      DECLARE @lastRow int
  
      SELECT 
         @firstRow = (@pageNumber - 1) * @itemsPerPage + 1,
         @lastRow = (@pageNumber - 1) * @itemsPerPage + @itemsPerPage,
         @totalRecords = (SELECT COUNT(p.[SDSID]) FROM v_SDS_Summary p);
  
      WITH ProductSummary AS
      (
          SELECT 
             p.[ID], p.Product, p.SecondName, p.Manufacturer, p.Category,
             ROW_NUMBER() OVER (ORDER BY p.ID ASC) AS RowNumber
          FROM v_Product_Summary p
      )
      SELECT 
         RowNumber, [ID], Product, SecondName, Manufacturer, Category
      FROM 
         ProductSummary 
      WHERE 
         RowNumber BETWEEN @firstRow AND @lastRow
      ORDER BY 
         --SDSID ASC
         CASE @sortByFieldName
             WHEN 'ID' THEN CAST(ID as varchar(50))
             WHEN 'Product' THEN Product
             WHEN 'CommonName' THEN SECONDNAME
             WHEN 'Manufacturer' THEN MANUFACTURER
             WHEN 'Category' THEN CATEGORY
         END
  END
  </code></pre>
  
  <p>When I run it and give it a page number to go to, it brings back the results great.</p>
  
  <p>When I tell it I want to sort by a column name, it seems to sort it in a weird way that I cannot even describe. basically seems to some how condense the sorting on that page only from a-z</p>
  
  <p><img src="https://i.stack.imgur.com/gc3UU.jpg" alt="Sorting issue"></p>
  
  <p>So here, this is sorted by Product in ASC but as you can see, the sorting is not correct. It seems to be sorted, which it technically is, but these are not the only products in the DB. There are around 3,000 products and definitely would be the page for "a" or "b" products to be shown on page number 2 as I specified.</p>
  
  <p>What am I doing wrong?</p>