October 05, 2014

MS SQL: Create Stored-Procedure to get Data as pagination

CREATE PROCEDURE GetProductLimit 
 @PageNum INT,   -- Ex: 1, 2, 3, 4
 @PageSize INT   -- Ex: 10 per page
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    DECLARE
  @fromNum int,
  @toNum int

  SET @fromNum = (@PageSize * (@pageNum - 1)) + 1
  SET @toNum = @pageSize * @pageNum

 ;WITH Product AS
 (
  SELECT ID, ProductName, [Description], Price, Qty,
  ROW_NUMBER() OVER (ORDER BY ID) AS RowNumber
  FROM dbo.Products 
 ) 
 SELECT ID, ProductName, [Description], Price, Qty  
 FROM Product 
 WHERE RowNumber BETWEEN @fromNum AND @toNum;
 END
GO

2 comments:

Anonymous said...

Why do you need an IF statement to separate the processing for pageNum = 1 and pageNum > 1?

IF @pageNum = 1 BEGIN
SET @fromNum = 1
SET @toNum = @pageSize * @pageNum
END

IF @pageNum > 1 BEGIN
SET @fromNum = (@PageSize * (@pageNum - 1)) + 1
SET @toNum = @pageSize * @pageNum
END

samnang said...

We don't need that condition.
Thanks!