在網上搜到如下存儲過程分頁的代碼:
create procedure splitpage @qry varchar(16384),@ipage int, @num int as
begin
declare @rcount int
declare @execsql varchar(16384)
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = stuff(@qry,charindex('select',@qry),6,'select sybid=identity(12),')
set @execsql = stuff(@execsql, charindex('from',@execsql),5,'into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
set rowcount 0
end
如果SQL比較復雜，如包含子查詢或使用union之類的SQL則會有問題，為了在公司的項目中使用，做了如下修改:
1.修改SQL,將select和from做標記，替換成selects和froms
create procedure splitpage @qry varchar(16384),@ipage int, @num int as
begin
declare @rcount int
declare @execsql varchar(16384)
select @rcount=@ipage*@num
set rowcount @rcount
set @execsql = str_replace(@qry,'selects','select sybid=identity(12),')
set @execsql = str_replace(@execsql, 'froms','into #temptable1 from')
set @execsql = @execsql || ' select * from #temptable1 where sybid>' || convert(varchar,(@ipage-1)*@num) || ' and sybid <= ' || convert(varchar,@ipage*@num)
execute (@execsql)
set rowcount 0
end
2.在分頁組件中增加parseSQL方法，在執行查詢前轉換SQL：
public String parseProcSql(String sql){
String stemp = sql.toUpperCase();
String v[] = stemp.split("UNION");
String result = "";
for(int i=0; i<v.length; i++){
int tmp = 0;
int from = 0;
tmp = v[i].indexOf("SELECT",0);
if(tmp>0){
v[i] = v[i].substring(0,tmp) + "SELECTS" + v[i].substring(tmp+6);
}else{
v[i] = "SELECTS" + v[i].substring(6);
}
if(i==0){
while(tmp!=-1){
from = v[i].indexOf("FROM",from+1);
tmp = v[i].indexOf("SELECT",tmp+1);
if(tmp>from)break;
}
result = v[i].substring(0,from) + "FROMS" + v[i].substring(from+4);
}else{
result = result + "UNION" + v[i];
}
}
return result;
}
3.使用union all分頁仍然會有問題.