jqgrid mysql 分页_sql-server-2008 – 如何在存储过程中为jqGrid进行分页？

有许多方法可以实现您需要的STORED PROCEDURE.例如,您可以在CTE SQL语句中使用ROW_NUMBER构造.

如果使用SQL Server 2012,则可以在ORDER BY之后使用OFFSET和FETCH来实现分页(请参阅here).在这种情况下,SQL语句看起来非常接近使用OFFSET和LIMIT的相应MySQL或PostgreSQL语句.顺便说一下,Microsoft Entity Framework使用Entity SQL Language具有紧密结构(SKIP和LIMIT).如果您使用SQL Server 2012或更高版本,可能OFFSET和FETCH将是首选方式.

因为您在问题中包含SQL Server 2008标记,所以我不会在我的答案中使用新的SQL Server 2012结构.

另一个好方法是使用sp_executesql,它允许您将SQL语句构造为带参数的字符串.它允许重用执行计划,这对于最佳性能非常重要.该方法允许您扩展STORED PROCEDURE的代码以实现服务器端过滤(搜索).

我看到需要在SQL语句中实现包含返回数据的ID(在您的情况下为PersonId)的分页.所以我决定建议你使用SELECT TOP结合LEFT OUTER JOIN的简化方法.

您存储的过程dbo.GetExtraPerson可以有两个类型为int的附加参数：@skip和@pageSize.如果@skip等于0,则STORED PROCEDURE可以执行

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

如果@skip不等于0,则相应的SQL语句可以如下

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

dbo.GetExtraPerson的完整代码可以是以下内容

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

上述过程另外返回记录总数,您可以使用它来分配totalRecords值.

如果将上述代码与sp_executesql结合使用,则可以轻松修改代码以在所有SELECT TOP语句中包含ORDER BY,以便返回的值与jqGrid中用户请求的排序顺序相对应.