Pagination with the stored procedure

Question

I am trying to add sorting feature of my pagination stored procedure.

How can I do this, so far I created this one. It works fine but when pass the @sort parameter, it didn't work.

ALTER PROCEDURE [dbo].[sp_Mk]
 @page INT,
 @size INT,
 @sort nvarchar(50) ,
 @totalrow INT  OUTPUT
AS
BEGIN
    DECLARE @offset INT
    DECLARE @newsize INT

    IF(@page=0)
    begin
       SET @offset = @page;
       SET @newsize = @size
    end
    ELSE 
    begin
        SET @offset = @page+1;
        SET @newsize = @size-1
    end
    -- SET NOCOUNT ON added to prevent extra result sets from
    SET NOCOUNT ON;
    WITH OrderedSet AS
    (
      SELECT *,
          ROW_NUMBER() OVER (ORDER BY @sort DESC) AS 'Index'
      FROM [dbo].[Mk]  
    )
   SELECT * 
   FROM OrderedSet 
   WHERE [Index] BETWEEN @offset AND (@offset + @newsize) 

   SET @totalrow = (SELECT COUNT(*) FROM [dbo].[Mk])
END

Answer 1

Try use this:

CREATE PROCEDURE dbo.proc_Paging_CTE_Dynamic
(
@Page int,
@RecsPerPage int,
@queryTxt varchar(MAX),
@orderBy varchar(MAX)
)
AS
-- The number of rows affected by the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON


DECLARE @sql NVARCHAR(MAX)

SET @sql = '
DECLARE @Page int, @RecsPerPage int
SET @Page = ' + CAST(@Page as varchar) + '
SET @RecsPerPage = ' + CAST(@RecsPerPage as varchar) + '
-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int
SELECT @FirstRec = (@Page - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1);

WITH TempResult as
(
SELECT ROW_NUMBER() OVER(ORDER BY ' + @orderBy + ') as RowNum,
  '
  +
  @queryTxt
  +
  '
)
SELECT top (@LastRec-1) *
FROM TempResult
WHERE RowNum > @FirstRec 
AND RowNum < @LastRec
'

EXECUTE (@sql)
-- Turn NOCOUNT back OFF
SET NOCOUNT OFF
GO

This way:

exec dbo.proc_Paging_CTE_Dynamic 126, 
    15, 
    'SUBSCRIBER.Number, SERVICE_MEMBER.AllowedDays, SERVICE_MEMBER.AllowedUntilTime, SERVICE_MEMBER.AllowedFromTime, SERVICE_MEMBER.Capacity, 
                      SERVICE.Name
        FROM SUBSCRIBER INNER JOIN
                      SERVICE_MEMBER ON SUBSCRIBER.Number = SERVICE_MEMBER.Subscriber_Number INNER JOIN
                      SERVICE ON SERVICE_MEMBER.Service_Id = SERVICE.Id', 
    'Number, Service_Id'

Answer 2

I found this link and it works pretty well. Does not use string / dynamic sql. Instead it uses CASE statement

SELECT ROW_NUMBER() OVER (ORDER BY
        CASE WHEN (@lSortCol = ‘ContactID’ AND @SortOrder=‘ASC’)
                    THEN ContactID
        END ASC,

http://www.codeproject.com/Articles/590341/Stored-Procedure-with-Sorting-Paging-and-Filtering

Answer 3

Try using dynamic SQL to fix this.

ALTER PROCEDURE [dbo].[sp_Mk]
  @page INT,
  @size INT,
  @sort nvarchar(50) ,
  @totalrow INT  OUTPUT
AS
BEGIN
    DECLARE @SQL nvarchar(4000)
    DECLARE @Params varchar(200)

SET @Params = N'@page int, @size int, @sort nvarchar(50), @totalrow int OUTPUT'

SET @SQL = '
DECLARE @offset INT
DECLARE @newsize INT

IF(@page=0)
begin
   SET @offset = @page;
   SET @newsize = @size
end
ELSE 
begin
    SET @offset = @page+1;
    SET @newsize = @size-1
end
-- SET NOCOUNT ON added to prevent extra result sets from
SET NOCOUNT ON;
WITH OrderedSet AS
(
  SELECT *,
      ROW_NUMBER() OVER (ORDER BY @sort  DESC) AS ''Index''
  FROM [dbo].[Mk]  
)
   SELECT * 
   FROM OrderedSet 
   WHERE [Index] BETWEEN @offset AND (@offset + @newsize) 

   SET @totalrow = (SELECT COUNT(*) FROM [dbo].[Mk])'


   EXEC sp_executesql @SQL, @Params, @page = @page, @size = @size, @sort = @sort, @totalrow = @totalrow 

END

Answer 4

Assuming@sort is the column name. try like this

WITH OrderedSet AS
(
 SELECT *,
     ROW_NUMBER() OVER (ORDER BY (CASE @sort WHEN 'column_name' 
                                             THEN column_name END ) DESC)  
        AS 'Index'
  FROM [dbo].[Mk] 

 )

instead of providing @sort variable put column name based on @sort. Hope this will work.

Answer 5

I'm adding an answer since so many of the other answers suggest dynamic SQL, which is not a best practice. You can add pagination using an OFFSET-FETCH clause, which provides you with an option to fetch only a window or page of results from a result set.

Note: OFFSET-FETCH can be used only with the ORDER BY clause.

Example:

SELECT First Name + ' ' + Last Name FROM Employees 
ORDER BY First Name 
OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;

Answer 6

createprocedure [dbo].[Procedurename]
@Id bigint,
@PageNumber int,
    @PageSize int,
    @Keyword nvarchar(100)
AS
Begin
set NoCount on;

IF(@PageNumber <=0)
    BEGIN
    SET @PageNumber = 1;
    END
    IF(@PageSize<=0)
    BEGIN
    SET @PageSize = 2147483647;
    END
    DECLARE @SkipRows int = (@PageNumber - 1) * @PageSize;


select * from tablename
where ('condition')
and (@Keyword is null or Name like '%'+ @Keyword + '%' or Description like '%'+@Keyword+'%')
order by Id asc 
OFFSET @SkipRows ROWS 
    FETCH NEXT @PageSize ROWS ONLY
return
End