/**********************************************************************
参数：@primarykey 主键，@orderby 排序字段,@sqlstr sql语句,@pagesize 每页显示的记录,@pageindex 当前页(从0开始)
***********************************************************************/
alter procedure [dbo].[pagequery]
@primarykey varchar(100),–主键
@orderby varchar(100),–排序字段
@sqlstr varchar(8000),–sql语句
@pagesize int,–每页显示的记录
@pageindex int–当前页(从1开始)
–@recordcount int output –返回的总记录数
as
declare @execsql varchar(8000)–要执行的sql组合
if @orderby=”
set @orderby=’order by ‘+@primarykey+’ asc’
else if (len(@orderby)<8 or upper(substring(@orderby,1,8))<>’order by’)
set @orderby=’order by ‘+@orderby
if @pagesize=-1–用于ajax的第一次查询
set @pagesize=0
set @pageindex=@pageindex-1
print(‘—–‘+cast(@pageindex as varchar(10)) )
begin
declare @recordcount int,@pagecount int
declare @s nvarchar(4000)
set @s = n’select @recordcount = count(‘+@primarykey+’) from (‘+@sqlstr+’) tn’
exec sp_executesql @s,n’@recordcount int output’,@recordcount output
if(@pagesize>0)
set @pagecount = (@recordcount – 1 + @pagesize) / @pagesize;–总页数
else
set @pagecount = 0;–总页数
if @pageindex<=0--如果是第一页就执行这个
begin
set @execsql='select top '+cast(@pagesize as varchar(100))+' * from ('+@sqlstr+') t '+@orderby
end
else
begin
if charindex('2000 - 8.00.',@@version)>0
begin
set @execsql=
'select top '+cast(@pagesize as varchar(100))+' *
from ('+@sqlstr+') as t where t.'+@primarykey+' not in
(select top '+cast((@pagesize*@pageindex) as varchar(100))+' '+@primarykey+'
from ('+@sqlstr+') t2 '+@orderby+') '+ @orderby
end
else
begin
set @execsql=
'select * from
(
select * from
(select *, #rownum#=row_number() over('+@orderby+') from ('+@sqlstr+') t1) t2 where t2.#rownum#>='+cast(@pagesize*@pageindex+1 as varchar(10))+' and t2.#rownum#<='+cast(@pagesize*(@pageindex+1) as varchar(10))+'
) t3
'
end
end
exec (@execsql+' select '+@recordcount+' as recordcount,'+@pagecount+' as pagecount ')
end