Sybase分页存储过程的实现

Sybase分页存储过程应该如何实现呢？这是很多人都提到的问题，下面就为您介绍Sybase分页存储过程的写法，希望可以让您对Sybase分页存储过程有更多的了解。

create procedure SP_PHP_PAGE @qry varchar(16384),@iStart int, @iLimit int, @sKeyFiled  varchar(32) as  
/*@qry SQL语句, @iStart 开始, @iLimit 结束,@sKeyFiled 表中的主键 */ 
begin 
declare @execSQL varchar(16384) 
declare @execSQLtmp varchar(16384) 
/*定义临时表表名*/ 
declare @dt varchar(10) --生成临时表的随机数 
set @dt=substring(convert(varchar, rand()), 3, 10)   --一个字符型的随机数 
set rowcount @iLimit    
if(@sKeyFiled is null) 
begin     
set @execSQL = stuff(@qry,charindex('select',@qry),6,'select number(*) as sybid,')            
set @execSQLtmp = ' select * from #temptable' + @dt + ' where sybid>' || convert(varchar,@iStart) || ' and sybid <= ' || convert(varchar,(@iStart/@iLimit+1)*@iLimit)   
end 
else 
begin 
set @execSQL = stuff(@qry,charindex('select',@qry),6,'select number(*) as sybid,' || @sKeyFiled || ' ,@' )           
set @execSQL =  stuff(@execSQL,charindex(',@',@execSQL),charindex('from',@execSQL)-charindex(',@',@execSQL),'' )           
set @execSQLtmp = ' select '|| @sKeyFiled ||' from #temptable' + @dt + ' where sybid>' || convert(varchar,@iStart) || ' and sybid <= ' || convert(varchar,(@iStart/@iLimit+1)*@iLimit)   
set @execSQLtmp = stuff(@qry,charindex('where',@qry),5,' where '|| @sKeyFiled || ' in ('|| @execSQLtmp ||') and ')    
end 
set @execSQL = stuff(@execSQL, charindex('from',@execSQL),4,'into #temptable' + @dt + ' from') 
select (@execSQL) as SQL, @execSQLtmp as SQLTmp 
set rowcount 0 
end 
 
调用
$sSQL = " exec SP_PHP_PAGE '$sSQL',$iStart,$iLimit,'iId'"; 
$pRow = $this->m_hDb->GetResult ( $sSQL ); 
$this->m_hDb->Excute ( $pRow->SQL ); 
$pData = $this->m_hDb->Select($pRow->SQLTmp);