配合dotNetFlexGrid的通用分页存储过程

通用分页存储过程
1通用分页存储过程说明
1.1存储过程名称：Common_Sp_Pagination
1.2存储过程代码：
ALTER PROC [dbo].[Common_Sp_Pagination]
@StrSql VARCHAR(MAX),
@PageIndex INT,--当前页码
@PageCount INT,--每页条数
@SortClause VARCHAR(100),--排序字段，不加“Order by”
@Filter VARCHAR(200),--条件，可以为空，不加where
@TotalNum INT OUTPUT--总记录条数
AS
DECLARE @ExeSql VARCHAR(MAX)
DECLARE @OrderByStr VARCHAR(100)
DECLARE @StartRowIndex INT
DECLARE @EndRowIndex INT
SET @StartRowIndex =( @PageIndex - 1 )* @PageCount + 1
SET @EndRowIndex = @PageIndex * @PageCount
/*--条件筛选--*/
IF @Filter IS NOT NULL
AND @Filter <>''
BEGIN
SET @StrSql = @StrSql +' WHERE '+ @Filter
END
/*--排序--*/
IF @SortClause IS NOT NULL
AND @SortClause <>''
BEGIN
SET @OrderByStr =' order by '+ @SortClause
SET @ExeSql =' SELECT * FROM ('+ @StrSql
+') AS A where rowIndex between '+CAST(@StartRowIndex AS VARCHAR) +' And '+CAST(@EndRowIndex AS VARCHAR)+' '+ @OrderByStr
END
ELSE
BEGIN
SET @ExeSql =' SELECT * FROM ('+ @StrSql
+') AS A where rowIndex between '+CAST(@StartRowIndex AS VARCHAR) +' And '+CAST(@EndRowIndex AS VARCHAR)+' '
END
PRINT @StrSql
PRINT @ExeSql
/*---查询总数-*/
CREATE TABLE #temp ( TotalNum INT)
DECLARE @InsertTemp VARCHAR(max)
SET@InsertTemp ='insert into #temp select count(*) from ('+@StrSql +') as A'
--PRINT @InsertTemp
EXEC(@InsertTemp)
SELECT @TotalNum = TotalNum
FROM #temp
DROP TABLE #temp
/*--查询分页信息--*/
EXEC(@ExeSql)
2举例说明：
2.1该例子是在CMS_INFO_SelectCmsInfoByInfoClassIdForPage存储过程中
调用通用的分页存错过程Common_Sp_Pagination,实现根据栏目编号分页获取栏
目信息
2.2主调用存储过程代码
CREATE PROC [dbo].[CMS_INFO_SelectCmsInfoByInfoClassIdForPage]
@InfoClassId VARCHAR(50),--所属栏目编号
@CurrentPage INT,--当前页码
@PageSize INT,--每页记录数量
@SortClause VARCHAR(2000),--不加'ORDER BY' 的排序语句
@TotalNum INT OUTPUT-- 总记录数量
AS
IF @SortClause IS NULL
OR @SortClause =''
BEGIN
SET @SortClause ='CreateDateTime desc'
END
DECLARE @StrSql VARCHAR(2000)
SET @StrSql ='SELECT
InfoId ,
InfoDelegate ,
InfoMainTitle ,
CreatePerson ,
CreateDateTime ,
STATUS,
ROW_NUMBER() OVER ( ORDER BY '+ @SortClause
+' ) AS rowIndex
FROM ( SELECT InfoId ,
InfoDelegate ,
InfoMainTitle ,
erName AS CreatePerson ,
CONVERT(VARCHAR(10), A.CreateDateTime, 120) AS CreateDateTime ,
CASE ( Status )
WHEN ''0'' THEN ''草稿''
WHEN ''1'' THEN ''有效''
ELSE ''不知名状态''
END AS Status
FROM dbo.CMS_INFO AS A WITH ( NOLOCK )
LEFT JOIN dbo.ADMIN_USER AS B WITH ( NOLOCK ) ON
a.CreatePerson =
erPKId
WHERE InfoClassId = '''+ @InfoClassId +'''
AND Status <> ''*''
) AS A'
EXEC mon_Sp_Pagination @StrSql, @CurrentPage, @PageSize, @SortClause,NULL, @TotalNum OUTPUT
3注意事项
3.1主调用存储过程的@StrSql中必须包含以下的语句，用户生成行号 ROW_NUMBER()
OVER ( ORDER BY xxxx) AS rowIndex，其中’xxxx’为排序字段,该列可以放
在查询字段的任意位置，为了配合华磊开发的新的dategrid，最好放在最后一列
3.2如果在@StrSql中涉及到多表查询，并以“表名.字段名”来查询字段的，最好为该
字段加上别名，同时在@SortClause中使用别名替代
3.3在@SortClause中出现的字段必须在查询字段中存在。