I think that you can limit the initial rows you get in the CTE if you order the query and use the TOP clause

;WITH emp AS (SELECT TOP (@PageNumber * @PageSize)

/* retrieve only a limited result set

if you need records 31 -> 60, you retrieve in the first step only 60 rows

*/

CASE

WHEN @SortOrder = 'Title' THEN ROW_NUMBER()OVER (ORDER BY Title)

WHEN @SortOrder = 'HireDate' THEN ROW_NUMBER()OVER (ORDER BY HireDate)

WHEN @SortOrder = 'City' THEN ROW_NUMBER()OVER (ORDER BY City)

-- In all other cases, assume that @SortOrder = 'LastName'

ELSE ROW_NUMBER()OVER (ORDER BY LastName)

END AS RecID,

LastName,

FirstName,

Title,

HireDate,

City,

Country,

PostalCode

FROM employees

WHERE

(@LastName IS NULL OR LastName LIKE '%' + @LastName + '%')

AND

(@Title IS NULL OR Title LIKE '%' + @Title + '%')

AND

(@City IS NULL OR City LIKE '%' + @City + '%')

/*specify order */

ORDER BY

CASE

WHEN @SortOrder = 'Title' THEN Title

WHEN @SortOrder = 'HireDate' THEN HireDate

WHEN @SortOrder = 'City' THEN City

ELSE LastName

END

)

SELECT

RecID, -- This column is only for debugging/testing purpose.

LastName,

Title,

HireDate,

City

FROM emp

/*

you need only the last @PageSize rows from your result set

*/

WHERE RecID > (@PageNumber - 1) * @PageSize)

ORDER BY RecID