Sybase分页存储过程实现

项目中需要用到Sybase数据库的分页功能，想尽各种办法都没有成功，最后用如下的存储过程成功实现功能，记录备忘。

 1 CREATE PROCEDURE jsp_query_page @sql varchar(2000),@start int, @pageSize int as 
 2 
 3 begin 
 4     declare @ipage int
 5     declare @rcount int 
 6     declare @execsql varchar(2000)
 7     declare @sql1 varchar(2000) 
 8     declare @t int,@p int,@n int,@l int
 9 
10     begin
11 
12         set @sql1=@sql
13         set @n=0
14         set @l=0
15         set @t=charindex('select ',lower(@sql))
16         set @sql=substring(@sql,@t+7,char_length(@sql)-7)
17         set @n=@n+1
18         set @l=@l+7
19         
20         while(@n!=0)
21         begin
22             set @t=charindex('select ',lower(@sql))
23             set @p=charindex('from ',lower(@sql))
24             if ((@t<@p) and (@t!=0))
25                 begin
26                     set @sql=substring(@sql,@t+7,char_length(@sql)-7)
27                     set @n=@n+1
28                     set @l=@l+6+@t
29                 end
30             else
31                 begin
32                     set @sql=substring(@sql,@p+5,char_length(@sql)-5)
33                     set @n=@n-1
34                     set @l=@l+4+@p
35                 end
36         end
37         set @execsql = substring(@sql1,1,@l-5)+' ,sybid=identity(12) into #temp '+substring(@sql1,@l-4,char_length(@sql1)-@l+5)
38         select @rcount=@start + @pageSize
39         set rowcount @rcount 
40         set @execsql = @execsql || ' select * from #temp where sybid>' || convert(varchar,@start) || ' and sybid <= ' || convert(varchar,@rcount) 
41         print @execsql
42         execute (@execsql) 
43         set rowcount 0 
44     end
45 end