Paged Data Example

--use mv;
--declare @PageSize int = 10; -- number of rows
--declare @PageNumber int = 1; -- 1 to x
--declare @Filter nvarchar(128) = '';
--declare @SortExpression nvarchar(128) = 'Elevation'
SELECT *, COUNT(*) OVER () as TotalRecords
FROM Peaks
WHERE [Description] LIKE '%' + @Filter + '%' or Comment LIKE '%' + @Filter + '%'
ORDER BY
case when @SortExpression = 'Description' then [Description] end,
case when @SortExpression = 'Description DESC' then [Description] end desc,
case when @SortExpression = 'Comment' then Comment end,
case when @SortExpression = 'Comment DESC' then Comment end desc,
case when @SortExpression = 'Latitude' then Latitude end,
case when @SortExpression = 'Latitude DESC' then Latitude end desc,
case when @SortExpression = 'Longitude' then Longitude end,
case when @SortExpression = 'Longitude DESC' then Longitude end desc,
case when @SortExpression = 'Elevation' then Elevation end,
case when @SortExpression = 'Elevation DESC' then Elevation end desc
OFFSET ( @PageNumber - 1 ) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY