通用存储过程高效分页

USE [pubs] GO -- 是否已经存在存储过程proc_Paging_Single IF EXISTS(SELECT * FROM sysobjects WHERE name='proc_Paging_Single') DROP PROCEDURE proc_Paging_Single GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- 通用分页存储过程(单表) CREATE PROCEDURE proc_Paging_Single( @tableName NVARCHAR(4000), -- 表或视图 @showColumn NVARCHAR(4000), -- 显示列，可以为* @primaryColumn NVARCHAR(100), -- 主键列 @currentPage INT, -- 当前页 @pageSize INT, -- 页记录数 @totalCount INT OUTPUT, -- 总记录数 @condition NVARCHAR(4000) -- 条件，例：id<500 and name='aa'(可以为空) ) AS BEGIN SET NOCOUNT ON DECLARE @sql NVARCHAR(4000), @sqlTotal NVARCHAR(4000), @row_ahead INT SET @row_ahead = (@currentPage - 1) * @pageSize SET @sql = 'SELECT TOP '+ cast(@pageSize as varchar(255)) + ' ' + @showColumn + ' FROM ' + @tableName +' WHERE ' IF LEN(@condition) > 0 SET @sql = @sql + '(' + @condition + ') AND ' SET @sql = @sql + @primaryColumn + ' NOT IN ( SELECT TOP ' + cast(@row_ahead as varchar(255)) + ' ' + @primaryColumn + ' FROM ' + @tableName IF LEN(@condition) > 0 SET @sql = @sql + ' WHERE (' + @condition + ')' SET @sql = @sql + ' ORDER BY ' + @primaryColumn + ') ORDER BY ' + @primaryColumn -- print @sql EXEC (@sql) -- 计算总记录数 SET @sqlTotal= 'SELECT @totalCount=COUNT(*) FROM ' + @tableName IF LEN(@condition) > 0 SET @sqlTotal = @sqlTotal + ' WHERE ' + @condition -- print @sql EXEC sp_executesql @sqlTotal,N'@totalCount INT OUTPUT',@totalCount OUTPUT END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- 是否已经存在存储过程proc_Paging_Much IF EXISTS(SELECT * FROM sysobjects WHERE name='proc_Paging_Much') DROP PROCEDURE proc_Paging_Much GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- 通用分页存储过程(无自动标识列的单表，多表) CREATE PROCEDURE proc_Paging_Much( @tableName NVARCHAR(4000), -- 表或视图 @showColumn NVARCHAR(4000), -- 显示列，可以为* @currentPage INT, -- 当前页 @pageSize INT, -- 页记录数 @totalCount INT OUTPUT, -- 总记录数 @condition NVARCHAR(4000) -- 条件，例：id<500 and name='aa'(可以为空) ) AS BEGIN SET NOCOUNT ON DECLARE @sql NVARCHAR(4000), @sqlTotal NVARCHAR(4000), @row_ahead INT SET @row_ahead = (@currentPage - 1) * @pageSize SET @sql = 'SELECT IDENTITY(INT,1,1) AS [PX],' + @showColumn + ' INTO #TempTable FROM ' + @tableName IF LEN(@condition) > 0 SET @sql = @sql + ' WHERE (' + @condition + ') ' SET @sql = @sql + ' SELECT TOP ' + cast(@pageSize as varchar(255)) + ' * FROM #TempTable WHERE [PX] NOT IN (SELECT TOP ' + cast(@row_ahead as varchar(255)) + ' [PX] FROM #TempTable ORDER BY [PX]) ORDER BY [PX] DROP TABLE #TempTable' -- print @sql EXEC (@sql) -- 计算总记录数 SET @sqlTotal= 'SELECT @totalCount=COUNT(*) FROM ' + @tableName IF LEN(@condition) > 0 SET @sqlTotal = @sqlTotal + ' WHERE (' + @condition + ') ' -- print @sql EXEC sp_executesql @sqlTotal,N'@totalCount INT OUTPUT',@totalCount OUTPUT END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO -- 测试（单表，无条件） declare @sum int exec proc_Paging_Single 'FY_Table1','*','id',1,10,@sum output,'' print @sum -- 测试（单表，带条件） declare @sum int exec proc_Paging_Single 'FY_Table1','*','id',1,10,@sum output,'id<51' print @sum -- 测试（无自动标识列的单表） declare @sum int exec proc_Paging_Much 'FY_Table2','*',1,10,@sum output,'' print @sum -- 测试（多表） declare @sum int exec proc_Paging_Much 'FY_Table1 fy1,FY_Table2 fy2','fy1.*',1,10,@sum output,'fy1.id=fy2.id' print @sum -- 测试（多表，inner join） declare @sum int exec proc_Paging_Much 'FY_Table1 fy1 inner join FY_Table2 fy2 on fy1.id=fy2.id','fy1.*',1,10,@sum output,'' print @sum -- 创建测试表1 CREATE TABLE FY_Table1( id INT IDENTITY(1,1), name VARCHAR(30), sex CHAR(2), age INT, PRIMARY KEY(id) ) GO -- 插入测试数据 DECLARE @i INT SET @i = 1 WHILE @i <= 1000 BEGIN INSERT INTO FY_Table1(name,sex,age) VALUES('丁可'+cast(@i as char),'男',21) SET @i = @i + 1 END GO -- 创建测试表2 CREATE TABLE FY_Table2( id INT, name VARCHAR(30), sex CHAR(2), age INT ) GO -- 插入测试数据 DECLARE @i INT SET @i = 1 WHILE @i <= 500 BEGIN INSERT INTO FY_Table2(id,name,sex,age) VALUES(@i,'丁可'+cast(@i as char),'男',21) SET @i = @i + 2 END GO