SQL Pagination

Posted on   |   In   |  
  1. 1. Pagination in Store Procedure

  • Pagination in Store Procedure


CREATE PROCEDURE [schema].[usp_GetTransactionPageable]
  (
    @id INT,
    @startDate DATETIME,
    @endDate DATETIME,
    @pageNum INT,
    @pageSize INT
  )
AS
  BEGIN
    DECLARE @idLocal INT
    DECLARE @startDateLocal DATETIME
    DECLARE @endDateLocal DATETIME
    SET @idLocal = @id
    SET @startDateLocal = @startDate
    SET @endDateLocal = DATEADD(DD, 1, @endDate)

    SELECT
      t.* FROM schema.TABLE t
    WHERE t.ID = @idLocal
          AND t.DATETIME >= @startDateLocal AND t.DATETIME < @endDateLocal
    ORDER BY t.DATETIME DESC
      OFFSET (@pageNum - 1) * @pageSize ROWS
      FETCH NEXT @pageSize ROWS ONLY
  END