Pagination with the stored procedure

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 didnt 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

View More Answer And Comment : Stackoverflow


Top 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

Top Answer 2

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

Top Answer 3

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

Top Answer 4

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

View More Answer And Comment : Stackoverflow

Android private release

I had made some Google search about this, but I didn`t find the best solution. I hope you can help. So I would like to release an app inside my company, we have more tha 500 employees. I found we [Read More...]

Select list not loading

I am trying to load data dynamically into my select but I seem to be doing something wrong because it is not working. This is the html: <select class="form-control" ng-options="item.id as [Read More...]

Add external JS to react component

I want to add a canvas to react component. Which I have achieved: var React = require('react'), c = require('./../canvas_animation'); var Home = React.createClass({ componentDidMount: function [Read More...]

Luaj parse table content

I just started using luaj in my project and I want to get the string content inside my lua table. For example: t = { subTitle = "Haircut", } return t; I want get the content of subtitle which [Read More...]

Catching errors in Global.asax

I have the following in my Global.aspx which is meant for handling errors: void Application_Error(object sender, EventArgs e) { Exception exception = Server.GetLastError(); if (exception != [Read More...]

Replacing not in with left join

Im looking to correct my syntax for a left join in a mySQL database. My query runs but is very slow and I'm not sure if my left join is correct. I'm needing to select the consumer_id against [Read More...]

Alternetive to TableView

I'm doing an app that retrives data from a server and show it in a view using a tableview. What i'd really like to do is a view showing the content like images side by side. Just like this [Read More...]