Apply OFFSET and FETCH NEXT to an existing stored procedure

Question

I have an existing stored procedure and I would like to apply the concept of paging by using OFFSET and FETCH NEXT.

However since this stored procedure already exists, and is called from other sources, I don't want to break it. I have added 2 optional parameters called @PageNumber INT = NULL and @NumberOfRecordsToRetrieve INT = NULL. I would like to know how to incorporate the logic into my query inside the stored procedure when these values are not supplied.

CREATE PROCEDURE TEST_PROCEDURE
    @Id INT,
    @PageNumber INT = NULL,
    @NumberOfRecordsToRetrieve INT = null
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @offsetRows INT, @nextRows INT, @maxCount INT

    IF(@PageNumber IS NOT NULL AND @NumberOfRecordsToRetrieve IS NOT NULL)
    BEGIN
        SET @offsetRows = (@PageNumber - 1) * @NumberOfRecordsToRetrieve;
        SET @nextRows = @NumberOfRecordsToRetrieve;
    END
    ELSE
    BEGIN
        SET @offsetRows = 0;
        SET @nextRows = 0;
    END

    SELECT 
        Id,
        Column2,
        Column3  
    FROM 
        ABCCustomers
    WHERE 
        Id = @Id

    UNION

    SELECT 
        Id,
        Column2,
        Column3  
    FROM 
        DEFCustomers
    WHERE 
        Id = @Id
    ORDER BY
        Column2
        -- this section needs to be applied only if the parameters have values
        OFFSET @offsetRows ROWS 
        FETCH NEXT @nextRows ROWS ONLY   
END

The offset and fetch next needs to be applied only when there is a value or else I will get an error. How do I solve this?

score 3 · Accepted Answer · answered Aug 21 '19 at 20:19

3

Simple, but perfectly acceptable:

IF @PageNumber IS NULL
  {Do query without OFFSET..FETCH}
ELSE
  {Do query with OFFSET..FETCH}

answered Aug 21 '19 at 20:19

Tab Alleman

31,483
7
36
52

Is there something simpler without repeating lines of code? – Fast Chip Aug 21 '19 at 20:30
Well there's this: https://stackoverflow.com/questions/12375837/offset-fetch-next-to-get-all-rows – Tab Alleman Aug 21 '19 at 20:33
Or dynamic SQL. – David Browne - Microsoft Aug 21 '19 at 20:44
As a very minor offshoot of this, you can just add this If/Else block to the statement in the query that outputs data. If you have 500 lines of temp table building and 50 lines of returning the results, you can just wrap the last 50 lines. – Cody McPheron Aug 21 '19 at 21:07

score 0 · Answer 2 · answered Jul 07 '20 at 13:33

If you do not want to double your SQL statment: COALESCE is a alternative.

select id,
       name
  from user
 order by id
 offset COALESCE(@offset,0) rows
 fetch next COALESCE(@pageSize,2147483647) rows only

Like this answer:

Using OFFSET-FETCH, how to default number of rows to "all rows"?

Apply OFFSET and FETCH NEXT to an existing stored procedure

2 Answers2