Stored Procedure and Paging with Ordering and filtering

I have data in a table and written a nice stored procedure which has a CTE and does paging wonderfully.

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.

Here is the stored procedure 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

When I run it and give it a page number to go to, it brings back the results great.

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

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.

What am I doing wrong?

Your CTE is always sorted by ID, so it will always use that to get the Page:

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
)

Then when on the page, you are sorting that page of results with this:

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

You should use the same ORDER BY in the CTE to truly order all the results by that column:

WITH ProductSummary AS
(
    SELECT p.[ID], p.Product, p.SecondName, p.Manufacturer, p.Category,
    ROW_NUMBER() OVER (ORDER BY 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 ASC) AS RowNumber
    FROM v_Product_Summary p
)