存储过程分页问题

xjc55 2007-11-02 10:25:32

利用存储过程分页时，统计行数一般用@totalCount=count(*)
但是如果利用（group by）分组后就不能统计了，得到的只是最后一个行的值
各位朋友，有没有什么办法解决？

如果您经常用存储过程分页，就可略去下面的代码

具体的存储过程如下：
CREATE PROCEDURE Pagination
@TotalCount INT OUTPUT,
@TotalPage INT OUTPUT,
@Table NVARCHAR(25), --将要查询的表名
@Column NVARCHAR(150),--将要查询的字段，可多列
@OrderColumn NVARCHAR(50), --排序字段
@CountColumn NVARCHAR(500), --取这个字段的最大值
@PageSize INT,--分页大小
@CurrentPage INT, --要查询的页
@OrderType NVARCHAR(4),--如何排序
@Condition NVARCHAR(2000) --查询条件
AS
DECLARE @PageCount INT,--总页数
@strSql NVARCHAR(4000), --主查询语句
@strTemp NVARCHAR(2000), --临时变量
@strCount NVARCHAR(1000),--统计语句
@strOrderType NVARCHAR(1000) --排序语句
BEGIN
IF @PageSize>80
BEGIN
SET @PageSize=80
END
SET @PageCount=@PageSize*(@CurrentPage-1)
IF @OrderType='DESC'
BEGIN
SET @strTemp='<(SELECT MIN'
SET @strOrderType=' ORDER BY ' + @OrderColumn +' DESC'
END
ELSE
BEGIN
SET @strTemp='>(SELECT MAX'
SET @strOrderType=' ORDER BY ' + @OrderColumn +' ASC'
END
IF @Condition!='NO'
BEGIN
IF @CurrentPage=1
BEGIN
SET @strCount='SELECT @TotalCount=COUNT(*) FROM '+ @Table + ' WHERE '+@Condition
SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table + '
WHERE ' + @Condition + ' '+@strOrderType
END
ELSE
BEGIN
SET @strCount='SELECT @TotalCount=COUNT(*) FROM '+ @Table + ' WHERE '+@Condition
SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table + '
WHERE ' + @CountColumn+' '+ @strTemp+ '('+ @CountColumn +')
FROM (SELECT TOP ' + str(@PageCount)+' '+ @CountColumn+'
FROM ' + @Table + ' WHERE '+@Condition+' '+@strOrderType+') AS T) AND '+ @Condition + ' '+@strOrderType
END
END
ELSE
BEGIN
IF @CurrentPage=1
BEGIN
SET @strCount='SELECT @TotalCount=COUNT(*) FROM '+ @Table
SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table +' '+@strOrderType
END
ELSE
BEGIN
SET @strCount='SELECT @TotalCount=COUNT(*) FROM '+ @Table
SET @strSql='SELECT TOP '+ str(@PageSize) +' '+ @Column +' FROM '+ @Table +' WHERE ' + @CountColumn+ ' '+ @strTemp+ '('+ @CountColumn +')
FROM (SELECT TOP ' + str(@PageCount)+' '+ @CountColumn+'
FROM ' + @Table + ' '+@strOrderType+') AS T) '+@strOrderType
END
END
EXEC sp_executesql @strCount,N'@TotalCount INT OUTPUT',@TotalCount OUTPUT
IF @TotalCount%@PageSize=0
BEGIN
SET @TotalPage=@TotalCount/@PageSize
END
ELSE
BEGIN
SET @TotalPage=@TotalCount/@PageSize+1
END
SET NOCOUNT ON
EXEC (@strSql)
END
SET NOCOUNT OFF
GO

...全文