fast_pagination.sql

-- =============================================
-- Author:  <Immanuel Garcia>
-- Create date: <March 30, 2026>
-- Description: <Proportional CTE-based request pagination for random page-access>
-- =============================================
CREATE PROCEDURE [dbo].[GetRequests]
 -- Add the parameters for the stored procedure here
 @pageNumber INT,
 @pageSize   INT,
 @requestStatus NVARCHAR(10),
 --OUTPUT Variables
 @isSuccess BIT OUTPUT,
 @message NVARCHAR(200) OUTPUT
AS
BEGIN
    WITH Keys AS
    (
        -- Number only the minimum rows required up to our target page
        SELECT TOP (@pageNumber * @pageSize)
            rn        = ROW_NUMBER() OVER (ORDER BY r.daterequested ASC),
            r.requestid                          -- primary key of requests
        FROM [FILETRACKER].[dbo].[requests] r 
        WHERE requestStatus LIKE @requestStatus + '%'
        ORDER BY r.daterequested ASC
    ),
    SelectedKeys AS
    (
        -- Slice out only the primary keys for the specific page we want
        SELECT TOP (@pageSize)
            SK.rn,
            SK.requestid
        FROM Keys SK
        WHERE SK.rn > ((@pageNumber - 1) * @pageSize)
        ORDER BY SK.rn ASC
    )
    -- Rejoin off-index columns only for the single page of rows we need
    SELECT
        SK.rn,
        r.*,
        rc.[firstname] + ' ' + rc.[lastname] AS [recipientName],
        vl.[locationname]
    FROM SelectedKeys SK
    JOIN  [FILETRACKER].[dbo].[requests]      r  ON r.[requestid]         = SK.requestid
    LEFT JOIN [FILETRACKER].[dbo].[recipients]   rc ON r.[recipientID] = rc.[id]
    LEFT JOIN [FILETRACKER].[dbo].[View_locations] vl ON r.[fileidref]  = vl.[fileid]
    ORDER BY r.[daterequested] ASC;
    SET @isSuccess = 1;
    SET @message = 'Listing completed.';
 RETURN
END