How to use to loop with paging in SQL Server query ?

Question

I have a SQL query which returns around 350,000 records (this can differ based in criteria). I am adding paging for better performance. What I want is, first load 1000 records and keep going until 350,000.

How can I achieve this?

• Get a count of a query and add a loop? If yes, how to set @Iterator value?
• Do I need to use ;WITH AS ()?

I am setting @PageSize count to total number of records I want (Is this correct?). Here I get 20412 records. If I set @PageSize to 1000 then not sure how to get next 1000 records and so on.

Query:

-- USING GENERAL PAGING

DECLARE @ConceptId INT, @PageNumber INT, @PageSize INT, @Iterator INT
SET @ConceptId = 2076
SET @PageNumber = 1
SET @PageSize = 
(
    SELECT COUNT(*)
    FROM ScheduledBulkUpdate(NOLOCK) SBU
    INNER JOIN ScheduledBulkUpdateRecurring(NOLOCK) SBR ON SBR.BatchId = SBU.BatchId
    WHERE SBU.ConceptId = @ConceptId
)
SET @Iterator = 0

PRINT @PageSize

--WHILE (@Iterator < @PageSize)
    --BEGIN

       SELECT DISTINCT SBU.BatchId AS Status,
            CAST(SBR.ExecutionTime AS DATE) AS StartRunDate,
            CAST(SBR.ExecutionTime AS TIME) AS StartRunTime,
            CAST(SBR.ExecutionTime AS DATE) AS EndRunDate,
            CAST(SBR.ExecutionTime AS TIME) AS EndRunTime,
            SBU.ScheduleID AS JobId,
            SBU.Name AS JobName,
             (
                SELECT concept
                FROM Concept
                WHERE conceptid = SBU.ConceptId
             ) AS ConceptName,
            SBU.ConceptId AS ConceptId,
            CAST(SBU.ScheduledStartDateTime AS  DATE) AS StartDate,
            CAST(SBU.ScheduledEndDateTime AS  DATE) AS EndDate,
            SBU.Frequency  AS Frequency,
            SBU.Query AS Query,
            SBU.Submitter AS Submitter
       FROM ScheduledBulkUpdate(NOLOCK) SBU
           INNER JOIN ScheduledBulkUpdateRecurring(NOLOCK) SBR ON SBR.BatchId = SBU.BatchId
       WHERE SBU.ConceptId = @ConceptId
       ORDER BY SBU.BatchId
       OFFSET @PageSize * (@PageNumber - 1) ROWS 
       FETCH NEXT @PageSize ROWS ONLY

       --Set @Iterator = @Iterator + 1
    --END

Answer 1

Try this

DECLARE @ConceptId INT = 2076, @PageNumber INT= 1, @PageSize INT = 1000, @totalPage int

SELECT @totalPage = (count(*)/@PageSize)+1
FROM ScheduledBulkUpdate(NOLOCK) SBU
    INNER JOIN ScheduledBulkUpdateRecurring(NOLOCK) SBR ON SBR.BatchId = SBU.BatchId
WHERE SBU.ConceptId = @ConceptId

PRINT @PageSize

WHILE (@totalPage >= @PageNumber)
BEGIN

    SELECT DISTINCT SBU.BatchId AS Status,
        CAST(SBR.ExecutionTime AS DATE) AS StartRunDate,
        CAST(SBR.ExecutionTime AS TIME) AS StartRunTime,
        CAST(SBR.ExecutionTime AS DATE) AS EndRunDate,
        CAST(SBR.ExecutionTime AS TIME) AS EndRunTime,
        SBU.ScheduleID AS JobId,
        SBU.Name AS JobName,
            (
            SELECT concept
            FROM Concept
            WHERE conceptid = SBU.ConceptId
            ) AS ConceptName,
        SBU.ConceptId AS ConceptId,
        CAST(SBU.ScheduledStartDateTime AS  DATE) AS StartDate,
        CAST(SBU.ScheduledEndDateTime AS  DATE) AS EndDate,
        SBU.Frequency  AS Frequency,
        SBU.Query AS Query,
        SBU.Submitter AS Submitter
    FROM ScheduledBulkUpdate(NOLOCK) SBU
        INNER JOIN ScheduledBulkUpdateRecurring(NOLOCK) SBR ON SBR.BatchId = SBU.BatchId
    WHERE SBU.ConceptId = @ConceptId
    ORDER BY SBU.BatchId
    OFFSET @PageSize * (@PageNumber - 1) ROWS 
    FETCH NEXT @PageSize ROWS ONLY

    set @PageNumber = @PageNumber + 1
END