这个存储过程返回一个记录集，同时还返回总的行数。
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)
