翻页的存储过程

2024-07-21 02:11:10

字体：大中小

来源：转载

供稿：网友

create proc turnpage
    @qcols varchar(200),                --需要查询的列
    @qtables         varchar(200),       --需要查询的表和条件
    @ikey         varchar (20),       --标识字段
    @okey        varchar(20),        --排序字段
    @pagesize         int,                 --每页的行数
    @pagenumber       int                   --要显示的页码, 从0开始
as
set nocount on
begin
    declare @sqltext as varchar(1000)
    declare @sqltable as varchar(1000)
    set @sqltable = 'select top ' + cast((@pagenumber + 1) * @pagesize as varchar(30)) + ' ' + @qcols +' from '+ @qtables + ' order by '[email protected]+ ' desc'
    set @sqltext =
        'select top ' + cast(@pagesize as varchar(30)) + ' * ' +
        'from (' + @sqltable + ') as tablea ' +
        'where ' + @ikey + ' not in(select top ' +
        cast(@pagenumber * @pagesize as varchar(30)) + ' ' + @ikey +
        ' from (' + @sqltable + ') as tableb)'
exec (@sqltext)
--print(@sqltext)
end
go

上一篇：sql优化器探讨

下一篇：SQL孤立用户解决方案