具体代码如下所示:
CREATE PROCEDURE p_GetPager
@TotalCount INT OUTPUT, --总记录数
@TotalPage INT OUTPUT, --总页数
@Table NVARCHAR(1000), --表名称,可多表连接,如:TbA a LEFT JOIN TbB b ON a.User=b.User
@Column NVARCHAR(1000), --查询列,可多列,如:*或者a.ID,b.UserName
@OrderColumn NVARCHAR(100), --排序字段,如:SortNo ASC或a.SortNo ASC,b.CrtTime DESC
@GroupColumn NVARCHAR(150), --分组字段,如:UserName或a.UserName,b.Time
@PageIndex INT, --当前页数
@PageSize INT, --每页记录数
@Group TINYINT, --是否启用分组
@Condition NVARCHAR(4000) --查询条件,如:SortNo>100或a.SortNo>100 AND b.UserName='张三'
AS
DECLARE @PageCount INT, --总页数
@strSql NVARCHAR(4000), --主查询语句
@strTemp NVARCHAR(2000), --临时变量
@strCount NVARCHAR(1000), --统计语句
@strOrderType NVARCHAR(1000); --排序语句
BEGIN
SET @PageCount = @PageSize * (@PageIndex - 1);
SET @strOrderType = N' ORDER BY ' + @OrderColumn + N' ';
IF @Condition != ''
BEGIN
IF @PageIndex = 1
BEGIN
IF @Group = 1
BEGIN
SET @strCount
= N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY '
+ @GroupColumn;
SET @strCount = @strCount + N' SET @TotalCount=@@ROWCOUNT';
SET @strSql
= N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' WHERE ' + @Condition
+ N' GROUP BY ' + @GroupColumn + N' ' + @strOrderType;
END;
ELSE
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition;
SET @strSql
= N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' WHERE ' + @Condition
+ N' ' + @strOrderType;
END;
END;
ELSE
BEGIN
IF @Group = 1
BEGIN
SET @strCount
= N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY '
+ @GroupColumn;
SET @strCount = @strCount + N' SET @TotalCount=@@ROWCOUNT';
SET @strSql
= N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
+ N') AS NUM FROM ' + @Table + N' WHERE ' + @Condition + N' GROUP BY ' + @GroupColumn
+ N') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
END;
ELSE
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' WHERE ' + @Condition;
SET @strSql
= N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
+ N') AS NUM FROM ' + @Table + N' WHERE ' + @Condition + N') AS T WHERE NUM BETWEEN '
+ STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
END;
END;
END;
ELSE
--没有查询条件
BEGIN
IF @PageIndex = 1
BEGIN
IF @Group = 1
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' GROUP BY ' + @GroupColumn;
SET @strCount = @strCount + N'SET @TotalCount=@@ROWCOUNT';
SET @strSql
= N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' GROUP BY '
+ @GroupColumn + N' ' + @strOrderType;
END;
ELSE
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table;
SET @strSql
= N'SELECT TOP ' + STR(@PageSize) + N' ' + @Column + N' FROM ' + @Table + N' ' + @strOrderType;
END;
END;
ELSE
BEGIN
IF @Group = 1
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table + N' GROUP BY ' + @GroupColumn;
SET @strCount = @strCount + N'SET @TotalCount=@@ROWCOUNT';
SET @strSql
= N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
+ N') AS NUM FROM ' + @Table + N' GROUP BY ' + @GroupColumn + N') AS T WHERE NUM BETWEEN '
+ STR(@PageCount + 1) + N' AND ' + STR(@PageCount + @PageSize);
END;
ELSE
BEGIN
SET @strCount = N'SELECT @TotalCount=COUNT(*) FROM ' + @Table;
SET @strSql
= N'SELECT * FROM (SELECT TOP (2000) ' + @Column + N',ROW_NUMBER() OVER(' + @strOrderType
+ N') AS NUM FROM ' + @Table + N') AS T WHERE NUM BETWEEN ' + STR(@PageCount + 1) + N' AND '
+ STR(@PageCount + @PageSize);
END;
END;
END;
EXEC sp_executesql @strCount,
N'@TotalCount INT OUTPUT',
@TotalCount OUTPUT;
IF @TotalCount > 2000
BEGIN
SET @TotalCount = 2000;
END;
IF @TotalCount % @PageSize = 0
BEGIN
SET @TotalPage = @TotalCount / @PageSize;
END;
ELSE
BEGIN
SET @TotalPage = @TotalCount / @PageSize + 1;
END;
SET NOCOUNT ON;
EXEC (@strSql);
END;