Pagination in SQL Server (Thank you https://www.sqlshack.com/pagination-in-sql-server/)

Select TOP(100) LastName, FirstName FROM Person.Person ORDER BY LastName

DECLARE @PageNumber AS INT
DECLARE @RowsOfPage AS INT
DECLARE @SortingCol AS VARCHAR(100) ='FirstName'
DECLARE @SortType AS VARCHAR(100) = 'ASC'
SET @PageNumber=1
SET @RowsOfPage=50
SELECT LastName, FirstName FROM Person.Person
ORDER BY
CASE WHEN @SortingCol = 'Lastname' AND @SortType ='ASC' THEN LastName END ,
CASE WHEN @SortingCol = 'LastName' AND @SortType ='DESC' THEN LastName END DESC,
CASE WHEN @SortingCol = 'FirstName' AND @SortType ='ASC' THEN FirstName END ,
CASE WHEN @SortingCol = 'FirstName' AND @SortType ='DESC' THEN FirstName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY

SET @PageNumber=2
SET @RowsOfPage=50
SET @SortingCol ='LastName'
SET @SortType = 'ASC'
SELECT LastName, FirstName FROM Person.Person
ORDER BY
CASE WHEN @SortingCol = 'Lastname' AND @SortType ='ASC' THEN LastName END ,
CASE WHEN @SortingCol = 'LastName' AND @SortType ='DESC' THEN LastName END DESC,
CASE WHEN @SortingCol = 'FirstName' AND @SortType ='ASC' THEN FirstName END ,
CASE WHEN @SortingCol = 'FirstName' AND @SortType ='DESC' THEN FirstName END DESC
OFFSET (@PageNumber-1)*@RowsOfPage ROWS
FETCH NEXT @RowsOfPage ROWS ONLY