存储过程实现sybase分页

create procedure test_p @ipage int, @num int as  

begin

        declare @maxpages int, @rcount int  

        if @ipage>=100

                select @maxpages=ceiling(count(*)/@num) from test

        else

                select @maxpages=100000

        if @ipage<[email protected]/2

        begin

                select @[email protected]*@num

                set rowcount @rcount

                select id=identity(12),name,descs,ddd into #temptable1 from test order by id

                select * from #temptable1  where id>=(@ipage-1)*@num and id<= @ipage*@num

        end else

        begin

                select @rcount=(@[email protected]+1)*@num

                set rowcount @rcount

                select id=identity(12),name,descs,ddd into #temptable2 from test order by id desc

                select id,name, ddd,descs from #temptable2  where id>=(@[email protected])*@num and id<= (@[email protected]+1)*@num                         order by id  desc

        end

end

create procedure splitpage @qry varchar(16384),@ipage int, @num int as  

begin

        declare @maxpages int

        declare @rcount int

        declare @execsql varchar(16384)

        if @ipage>=100

                select @maxpages=ceiling(count(*)/@num) from test

        else

                select @maxpages=100000

        if @ipage<[email protected]/2

        begin

                select @[email protected]*@num

                set rowcount @rcount

                set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')

                set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')

                set @execsql = @execsql || ' select * from #temptable1  where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)

                execute (@execsql)

        end else

        begin

                select @rcount=(@[email protected]+1)*@num

                set rowcount @rcount

                set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')

                set @execsql = stuff(@execsql, charindex('from',@execsql),4,'into #temptable1 from')

                set @execsql = @execsql || ' order by sybid desc'

                set @execsql = @execsql || ' select * from #temptable1 where sybid > ' || convert(varchar,(@[email protected]ge)*@num) || ' and sybid <= ' || convert(varchar,(@[email protected]+1)*@num)

                execute (@execsql)

        end

end