MsSql 存储过程分页代码 [收集多篇]

--使用说明 本代码适用于mssql2000,对于其它数据库也可用.但没必要
--创建存储过程
create procedure pagination
@tblname varchar(255), -- 表名
@strgetfields varchar(1000) = '*', -- 需要返回的列
@fldname varchar(255)='', -- 排序的字段名(可包含如table.fldname形式）
@pagesize int = 10, -- 页尺寸
@pageindex int = 1, -- 页码
@docount bit = 0, -- 返回记录总数, 非 0 值则返回
@ordertype bit = 0, -- 设置排序类型, 非 0 值则降序
@strwhere varchar(1500) = '' -- 查询条件 (注意: 不要加 where)
as
declare @strsql varchar(5000) -- 主语句
declare @strtmp varchar(110) -- 临时变量
declare @strorder varchar(400) -- 排序类型
declare @fldname_t varchar(255) -- 在分页时用的排序字段名，不包含多表并列时的表名
set @fldname_t = right(@fldname,len(@fldname)-charindex('.',@fldname))
if @docount != 0
begin
if @strwhere !=''
set @strsql = 'select count(*) as total from ' + @tblname + ' where '+@strwhere
else
set @strsql = 'select count(*) as total from ' + @tblname + ''
end
--以上代码的意思是如果@docount传递过来的不是0，就执行总数统计。以下的所有代码都是@docount为0的情况
else
begin
if @ordertype != 0
begin
set @strtmp = '<(select min'
set @strorder = ' order by ' + @fldname +' desc'
--如果@ordertype不是0，就执行降序，这句很重要！
end
else
begin
set @strtmp = '>(select max'
set @strorder = ' order by ' + @fldname +' asc'
end
if @pageindex = 1
begin
if @strwhere != ''
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from ' + @tblname + ' where ' + @strwhere + ' ' + @strorder
else
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '+ @tblname + ' '+ @strorder
--如果是第一页就执行以上代码，这样会加快执行速度
end
else
begin
--以下代码赋予了@strsql以真正执行的sql代码
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '+ @tblname + ' where ' + @fldname + ' ' + @strtmp + ' ('+ @fldname_t + ') from (select top ' + str((@pageindex-1)*@pagesize) + ' '+ @fldname + ' from ' + @tblname + '' + @strorder + ') as tbltmp)'+ @strorder
if @strwhere != ''
set @strsql = 'select top ' + str(@pagesize) +' '+@strgetfields+ ' from '+ @tblname + ' where ' + @fldname + ' ' + @strtmp + ' ('+ @fldname_t + ') from (select top ' + str((@pageindex-1)*@pagesize) + ' '+ @fldname + ' from ' + @tblname + ' where ' + @strwhere + ' '+ @strorder + ') as tbltmp) and ' + @strwhere + ' ' + @strorder
end
end
exec (@strsql)
go
--测试
create table news --建表
(
n_id int identity(1,1) primary key,
n_title char(200),
n_content text
)
--写循环插入1000000条的数据
create proc tt
as
declare @i int
set @i=0
while(@i<1000000)
begin
insert into news(n_title,n_content) values('sb','dsfsdfsd')
set @i=@i+1
end
exec tt
exec pagination 'news','*','n_id',1000,2,0,0,''

第二篇

自己改写的一个分页存储过程
create proc paging
(
@pagesize int,
@pageindex int,
@pagefield nvarchar(32),
@counttotal bit=1,
@fieldquery nvarchar(512),
@tablequery nvarchar(512),
@wherequery nvarchar(2048),
@orderquery nvarchar(512)
)
as
declare @bdate datetime
set @bdate = getdate()
declare @itemcount int
set @itemcount=@pageindex*@pagesize
declare @itemlowwer int
set @itemlowwer=(@pageindex-1)*@pagesize
declare @cmd nvarchar(3062)

if @pageindex=1
set @cmd ='select top ‘+cast(@pagesize as nvarchar)+' ‘+@fieldquery+' from ‘+@tablequery+' where ‘+@wherequery+' order by ‘+@orderquery
else
set @cmd='select ‘+@fieldquery+' from ‘+@tablequery+' where ‘+@pagefield+' in (select top ‘+cast(@itemcount as nvarchar)+' ‘+@pagefield+' from ‘+@tablequery+' where ‘+@wherequery+' order by ‘+ @orderquery+')
and ‘+@pagefield+' not in (select top ‘ +cast(@itemlowwer as nvarchar)+' ‘+@pagefield+' from ‘+@tablequery+' where ‘+@wherequery+' order by ‘+ @orderquery+')'
–print @cmd
exec(@cmd)

select datediff( ms , @bdate , getdate() )

if @counttotal =1
begin
set @cmd = ‘select count( 0) from ‘+@tablequery+' where ‘+@wherequery
exec(@cmd)
end
go