这个本人现在有个一个分页代码，在有多个字段排序的时候性能觉的还可以，比临时表快多了，希望有兴趣的朋友测试一下，如果有不同意见请大家拍砖，谢谢指点。
create proc [dbo].[sc_searchjianli]
@PageSize int, --页大小
@intPage int, --当前页数
@strwhere nvarchar(1000) --查询条件
as
begin
set nocount on
declare @TotalCount int --总数量
declare @PageCount int --总页数
declare @LowerBound int --定义此页的底码
declare @UpperBound int --定义此页的顶码
declare @varSql nvarchar(3000)
--计算总个数
set @varsql='select @TotalCount=count(1)
from jianli a join personinfo b on a.loginid=b.loginid join jiaoyu c on a.loginid=c.loginid join yingpininfo d on a.loginid=d.loginid
where a.jlzt=0'+ @strwhere
exec sp_executesql @varsql, N'@totalcount int output',@totalcount output
--计算总页数
if @TotalCount % @PageSize=0
begin
set @PageCount=@TotalCount/@PageSize
end
else
begin
set @PageCount=@TotalCount/@PageSize +1
end
--分页提取数据
set @LowerBound=(@intpage-1)* @pagesize+1
set @UpperBound=@LowerBound + @pagesize-1
set @varsql='declare @table table (id int identity(1,1) ,nid int);'
set @varsql=@varsql+'insert into @table(nid) select top '+str(@UpperBound)+' a.id from jianli a join personinfo b on a.loginid=b.loginid join jiaoyu c on a.loginid=c.loginid join yingpininfo d on a.loginid=d.loginid where a.jlzt=0 '+ @strwhere +' order by a.riqi desc;'
set @varsql=@varsql+'select a.loginid,b.name,b.sex,datediff(yy,b.csny,getdate()) as birthdate,b.workyear,c.zhuanye,c.school,c.xueli ,a.riqi
from jianli a join personinfo b on a.loginid=b.loginid join jiaoyu c on a.loginid=c.loginid join yingpininfo d on a.loginid=d.loginid join @table m on a.id=m.nid
where m.id between'+ str(@LowerBound)+' and '+str(@UpperBound)+' and a.jlzt=0
order by a.riqi desc'
select @TotalCount,@PageCount
exec sp_executesql @varsql
end
posted @ 2008-05-25 16:58
wangwang0522 阅读(758)
评论(8) 编辑 收藏 所属分类:
sql server性能优化