Posted on 2009-07-21 13:48
Yuanhua 阅读(498)
评论(0) 编辑 收藏
在网上搜到如下存储过程分页的代码:
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分页仍然会有问题.