title: Attempting to return more rows than what is available
body: <p>Below is an example of what my stored procedure looks like. 
  One problem occurs when a user attempts to fetch a number of rows (@PageSize) more than what is currently available in the <strong>last page</strong> (@PageNumber), then the returned result is some how zero, instead of returning what is available even if less than the passed PageSize param. </p>
  
  <p><strong>This call returns result</strong></p>
  
  <pre><code>exec [dbo].[CustomersPaginationProc] @LocationID ='0',@PageNumber=17999,@PageSize=10,@SortColumn=N'Name',@SortOrder=N'ASC'
  </code></pre>
  
  <p><strong>while this call does not</strong></p>
  
  <pre><code>exec [dbo].[CustomersPaginationProc] @LocationID ='0',@PageNumber=17999,@PageSize=20,@SortColumn=N'Name',@SortOrder=N'ASC'
  </code></pre>
  
  <p><strong>Procedure detail:</strong></p>
  
  <pre><code>    ALTER PROCEDURE [CustomersPaginationProc]
      -- Add the parameters for the stored procedure here
      @LocationID VARCHAR(50) = NULL
      @PageNumber INT = NULL, 
      @PageSize INT = NULL,
      @SortColumn NVARCHAR(20) = NULL,
      @SortOrder NVARCHAR(4) = NULL
   AS BEGIN
   SET NOCOUNT ON;
  
   WITH CTE_Results AS 
  (
       SELECT 
            cs.LocationID
          , cs.Name
       FROM Customers cs with (nolock)  
       WHERE
          (@LocationID IS NULL OR cs.LocationID LIKE '%' + @LocationID + '%')
  ),
  CTE_TotalRows AS 
  (
       SELECT COUNT(*) AS MaxRows FROM CTE_Results
  )
  SELECT * FROM CTE_Results, CTE_TotalRows
       ORDER BY
              CASE WHEN (@SortColumn IS NOT NULL AND @SortColumn = 'LocationID' AND @SortOrder= 'ASC')
                          THEN CTE_Results.LocationID
              END ASC
        OFFSET @PageSize * (@PageNumber) ROWS
        FETCH NEXT @PageSize ROWS ONLY
        OPTION (RECOMPILE)
  END
  </code></pre>