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

@qtype varchar(50) = null,
    @page int = 0,
    @limit int = 0

SELECT * FROM [dbo].[post_image_tbl] WHERE u_id IN(SELECT f_id FROM [dbo].[followers_tbl] WHERE u_id = u_id) and
    @limit  BETWEEN ( @page - 1 ) * @limit + 1 AND @page * @limit
     ORDER BY id DESC

Answer 2

One way (possibly not the best) to do it is to use dynamic SQL

CREATE PROCEDURE [sp_Mk]
 @page INT,
 @size INT,
 @sort nvarchar(50) ,
 @totalrow INT  OUTPUT
AS
BEGIN
    DECLARE @offset INT
    DECLARE @newsize INT
    DECLARE @sql NVARCHAR(MAX)

    IF(@page=0)
      BEGIN
        SET @offset = @page
        SET @newsize = @size
       END
    ELSE 
      BEGIN
        SET @offset = @page*@size
        SET @newsize = @size-1
      END
    SET NOCOUNT ON
    SET @sql = '
     WITH OrderedSet AS
    (
      SELECT *, ROW_NUMBER() OVER (ORDER BY ' + @sort + ') AS ''Index''
      FROM [dbo].[Mk] 
    )
   SELECT * FROM OrderedSet WHERE [Index] BETWEEN ' + CONVERT(NVARCHAR(12), @offset) + ' AND ' + CONVERT(NVARCHAR(12), (@offset + @newsize)) 
   EXECUTE (@sql)
   SET @totalrow = (SELECT COUNT(*) FROM [Mk])
END

Here is SQLFiddle demo

Answer 3

the best option is to use CTE, this will have less overhead and doable option below is a sample that can be useful , here we can use the ROW_NUMBER() function to fetch only the relevant rows:

    CREATE PROCEDURE dbo.uspItemsPaging
(
@Page int,
@RecsPerPage int 

)
AS
-- The number of rows affected by the different commands
-- does not interest the application, so turn NOCOUNT ON
SET NOCOUNT ON


-- Determine the first record and last record 
DECLARE @FirstRec int, @LastRec int

SELECT @FirstRec = (IIF ( @Page > 0 , @Page, 1 ) - 1) * @RecsPerPage - 1) * @RecsPerPage
SELECT @LastRec = (@Page * @RecsPerPage + 1);

WITH TempResult as
(
SELECT ROW_NUMBER() OVER(ORDER BY Items.ID DESC) as RowNum,
    Items.*  


     From dbo.Items



)


SELECT top (@LastRec-1) *
FROM TempResult
WHERE RowNum > @FirstRec 
AND RowNum < @LastRec



-- Turn NOCOUNT back OFF
SET NOCOUNT OFF