MSSQL分页存储过程完整示例(支持多表分页存储)
程序员文章站
2022-03-09 23:49:15
本文实例讲述了mssql分页存储过程。分享给大家供大家参考,具体如下:
use [db_common]
go
/****** 对象: storedproced...
本文实例讲述了mssql分页存储过程。分享给大家供大家参考,具体如下:
use [db_common] go /****** 对象: storedprocedure [dbo].[com_pagination] 脚本日期: 03/09/2012 23:46:20 ******/ set ansi_nulls on go set quoted_identifier on go /************************************************************ * *sql分页存储过程(支持多表分页存储) * *调用实例: exec com_pagination 100, --总记录数 0, --总页数 -- 'person',--查询的表名 ' person p left join te a on a.pid=p.id ', --查询的表名(这里为多表) 'a.*', --查询数据列 'p.id', --排列字段 'p.id', --分组字段 2, --每页记录数 1, --当前页数 0, --是否使用分组,否是 ' a.pid=2'--查询条件 ************************************************************/ create procedure [dbo].[com_pagination] @totalcount int output, --总记录数 @totalpage int output, --总页数 @table nvarchar(1000), --查询的表名(可多表,例如:person p left join te a on a.pid=p.id ) @column nvarchar(1000), --查询的字段,可多列或者为* @ordercolumn nvarchar(100), --排序字段 @groupcolumn nvarchar(150), --分组字段 @pagesize int, --每页记录数 @currentpage int, --当前页数 @group tinyint, --是否使用分组,否是 @condition nvarchar(4000) --查询条件(注意:若这时候为多表查询,这里也可以跟条件,例如:a.pid=2) as declare @pagecount int, --总页数 @strsql nvarchar(4000), --主查询语句 @strtemp nvarchar(2000), --临时变量 @strcount nvarchar(1000), --统计语句 @strordertype nvarchar(1000) --排序语句 begin set @pagecount = @pagesize * (@currentpage -1) set @strordertype = ' order by ' + @ordercolumn + ' ' if @condition != '' begin if @currentpage = 1 begin if @group = 1 begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' where ' + @condition + ' group by ' + @groupcolumn set @strcount = @strcount + ' set @totalcount=@@rowcount' set @strsql = 'select top ' + str(@pagesize) + ' ' + @column + ' from ' + @table + ' where ' + @condition + ' group by ' + @groupcolumn + ' ' + @strordertype end else begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' where ' + @condition set @strsql = 'select top ' + str(@pagesize) + ' ' + @column + ' from ' + @table + ' where ' + @condition + ' ' + @strordertype end end else begin if @group = 1 begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' where ' + @condition + ' group by ' + @groupcolumn set @strcount = @strcount + ' set @totalcount=@@rowcount' set @strsql = 'select * from (select top (2000) ' + @column + ',row_number() over(' + @strordertype + ') as num from ' + @table + ' where ' + @condition + ' group by ' + @groupcolumn + ') as t where num between ' + str(@pagecount + 1) + ' and ' + str(@pagecount + @pagesize) end else begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' where ' + @condition set @strsql = 'select * from (select top (2000) ' + @column + ',row_number() over(' + @strordertype + ') as num from ' + @table + ' where ' + @condition + ') as t where num between ' + str(@pagecount + 1) + ' and ' + str(@pagecount + @pagesize) end end end else --没有查询条件 begin if @currentpage = 1 begin if @group = 1 begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' group by ' + @groupcolumn set @strcount = @strcount + 'set @totalcount=@@rowcount' set @strsql = 'select top ' + str(@pagesize) + ' ' + @column + ' from ' + @table + ' group by ' + @groupcolumn + ' ' + @strordertype end else begin set @strcount = 'select @totalcount=count(*) from ' + @table set @strsql = 'select top ' + str(@pagesize) + ' ' + @column + ' from ' + @table + ' ' + @strordertype end end else begin if @group = 1 begin set @strcount = 'select @totalcount=count(*) from ' + @table + ' group by ' + @groupcolumn set @strcount = @strcount + 'set @totalcount=@@rowcount' set @strsql = 'select * from (select top (2000) ' + @column + ',row_number() over(' + @strordertype + ') as num from ' + @table + ' group by ' + @groupcolumn + ') as t where num between ' + str(@pagecount + 1) + ' and ' + str(@pagecount + @pagesize) end else begin set @strcount = 'select @totalcount=count(*) from ' + @table set @strsql = 'select * from (select top (2000) ' + @column + ',row_number() over(' + @strordertype + ') as num from ' + @table + ') as t where num between ' + str(@pagecount + 1) + ' 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 set nocount off /**调用实例: exec com_pagination 100, --总记录数 0, --总页数 -- 'person',--查询的表名 ' person p left join te a on a.pid=p.id ', --查询的表名(这里为多表) 'a.*', --查询数据列 'p.id', --排列字段 'p.id', --分组字段 2, --每页记录数 1, --当前页数 0, --是否使用分组,否是 ' a.pid=2'--查询条件 select a.* from person p left join te a on a.pid = p.id where a.pid = 2 **/
希望本文所述对大家sql server数据库程序设计有所帮助。