How to do Paging for jqGrid in Stored Procedure?

Question

I'm not good at sql, but I have to do paging for jqGrid in my stored procedure which has many records. My asp.net mvc3 controller code as follows,

[HttpPost]
    public JsonResult GetExtraPersons(int cId, long pId, JQGridSettings gridSettings)
    {
        List<ExtraPerson> extraPersons = new List<ExtraPerson>();            
        ExtraPersonViewModel extraPersonViewModel = new ExtraPersonViewModel();
        extraPersonViewModel.CampId = cId;
        extraPersonViewModel.ReferencePatientId = pId;

        extraPersons = ExtraPersonService.GetExtraPersons(extraPersonViewModel.CampId, extraPersonViewModel.ReferencePatientId);

        int pageIndex = gridSettings.pageIndex;
        int pageSize = gridSettings.pageSize;
        int totalRecords = extraPersons.Count;
        int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

        int startRow = (pageIndex - 1) * pageSize;
        int endRow = startRow + pageSize;

        var jsonData = new
        {
            total = totalPages,
            page = pageIndex,
            records = totalRecords,
            rows =
            (
              extraPersons.Select(e => new
              {
                  Id = e.ExtraPersonId,                
                  FirstName = e.FirstName,
                  LastName = e.LastName,
                  MobilePhone = e.MobileNumber,
                  Email = e.EmailId,
                  PersonalNumber = e.PersonNumber,
                  Diabetes = e.Diabetes, 
                  BloodPressure = e.BloodPressure,
              })
            ).ToArray()
        };
        return Json(jsonData);
    }

as well as my stored procedure in sql server 2008 as follows,

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetExtraPerson] 
(
@CampId INT,
@ReferencePatientId BIGINT
)


AS  

BEGIN  
SET NOCOUNT ON         

SELECT  

PERS.PersonId,  
PERS.FirstName,  
PERS.LastName,  
PERS.MobileNumber,
PERS.EmailId,
PERS.PersonNumber,
E.ExtraPersonId,
E.Diabetes,
E.BloodPressure

FROM  
ExtraPerson E  
INNER JOIN Person PERS  
ON PERS.PersonId=E.PersonId  



WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted = 0
END

Now jqGrid is working properly except paging. For ex: If it has 15 records, the first page shows 10 records, remaining is in second page but I cant go to it. Can anyone suggest me, how to do paging for jqgrid?

Answer 1

There are many way to implement STORED PROCEDURE which you need. For example you can use ROW_NUMBER construction inside of CTE SQL statement.

If you use SQL Server 2012 you can use OFFSET and FETCH after ORDER BY to implement pagination (see here). In the case the SQL statement will be look very close to the corresponding MySQL or PostgreSQL statements which uses OFFSET and LIMIT. By the way Microsoft Entity Framework use Entity SQL Language having close construct (SKIP and LIMIT). Probably OFFSET and FETCH would be preferred way if you use SQL Server 2012 or higher.

Because you included SQL Server 2008 tag in your question I would not use new SQL Server 2012 constructs in my answer.

One more good way would be to use sp_executesql which allows you to construct an SQL statement as string with parameters. It allows to reuse execution plans which is very important for the best performance. The approach allows you to extend the code of your STORED PROCEDURE to implement server side filtering (searching).

I see that need to implement pagination in the SQL statement which contain ID of the returned data (PersonId in your case). So I decide to suggest you to use simplified way which use SELECT TOP in combination with LEFT OUTER JOIN.

You STORED PROCEDURE dbo.GetExtraPerson can have two additional parameters of type int: @skip and @pageSize. In case of @skip is equal to 0 the STORED PROCEDURE can just execute

SELECT TOP (@pageSize) PERS.PersonId
    ,PERS.FirstName
    ,PERS.LastName
    ,PERS.MobileNumber
    ,PERS.EmailId
    ,PERS.PersonNumber
    ,E.ExtraPersonId
    ,E.Diabetes
    ,E.BloodPressure
FROM ExtraPerson E  
    INNER JOIN Person PERS ON PERS.PersonId=E.PersonId  
WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted=0

If @skip is not equal to 0 then the corresponding SQL statement can be the following

WITH GetAll AS (
    SELECT PERS.PersonId
        ,PERS.FirstName
        ,PERS.LastName
        ,PERS.MobileNumber
        ,PERS.EmailId
        ,PERS.PersonNumber
        ,E.ExtraPersonId
        ,E.Diabetes
        ,E.BloodPressure
    FROM ExtraPerson E  
        INNER JOIN Person PERS ON PERS.PersonId=E.PersonId  
    WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId AND E.IsDeleted=0
),GetFirst AS (
    SELECT TOP (@skip) *
    FROM GetAll
    ORDER BY Name
),GetNext AS (
    SELECT TOP (@pageSize) a.*
    FROM GetAll AS a
        LEFT OUTER JOIN GetFirst AS f ON f.Id=a.Id
    WHERE f.Id IS NULL
    ORDER BY Name
)
SELECT * FROM GetNext 

The full code of dbo.GetExtraPerson could be about the following

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE dbo.GetExtraPerson 
    @CampId int,
    @ReferencePatientId bigint,
    @skip int,
    @pageSize int
AS
BEGIN
    DECLARE @records int;
    SET NOCOUNT ON;

    SET @records = (SELECT COUNT(*)
                    FROM ExtraPerson E  
                        INNER JOIN Person PERS ON PERS.PersonId=E.PersonId  
                    WHERE E.CampId=@CampId
                        AND ReferencePatientId=@ReferencePatientId
                        AND E.IsDeleted=0);

    IF @skip <= 0
        SELECT TOP (@pageSize) PERS.PersonId
            ,PERS.FirstName
            ,PERS.LastName
            ,PERS.MobileNumber
            ,PERS.EmailId
            ,PERS.PersonNumber
            ,E.ExtraPersonId
            ,E.Diabetes
            ,E.BloodPressure
        FROM ExtraPerson E  
            INNER JOIN Person PERS ON PERS.PersonId=E.PersonId  
        WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId
            AND E.IsDeleted=0
    ELSE
        WITH GetAll AS (
            SELECT PERS.PersonId
                ,PERS.FirstName
                ,PERS.LastName
                ,PERS.MobileNumber
                ,PERS.EmailId
                ,PERS.PersonNumber
                ,E.ExtraPersonId
                ,E.Diabetes
                ,E.BloodPressure
            FROM ExtraPerson E  
                INNER JOIN Person PERS ON PERS.PersonId=E.PersonId  
            WHERE E.CampId=@CampId AND ReferencePatientId=@ReferencePatientId
                AND E.IsDeleted=0
        ),GetFirst AS (
            SELECT TOP (@skip) *
            FROM GetAll
            ORDER BY Name
        ),GetNext AS (
            SELECT TOP (@pageSize) a.*
            FROM GetAll AS a
                LEFT OUTER JOIN GetFirst AS f ON f.Id=a.Id
            WHERE f.Id IS NULL
            ORDER BY Name
        )
        SELECT * FROM GetNext;

    RETURN @records;
END
GO

The procedure above returns the total number of records additionally and you can use it to assign totalRecords value.

If you would use above code in combination with sp_executesql you can easy modify the code to include ORDER BY in all SELECT TOP statements so that the returned values will corresponds to sorting order requested by the user in jqGrid.