分页存储过程(asp.net中同样适用)

在这里,感谢所有csdn blog上贴过相关主题贴子的兄弟,下面的方法是建立在csdn blog上七篇关于分页存储过程的基础上的

前段时间研究分页的时候,在csdn的blog上看到了一位兄弟写的分页存储过程,发现非常好,于是,就使用了这个存储过程,下面是原版的分页存储过程

--开始
create procedure getrecordfrompage
    @tblname      varchar(255),       -- 表名
    @fldname      varchar(255),       -- 字段名
    @pagesize     int = 10,           -- 页尺寸
    @pageindex    int = 1,            -- 页码
    @iscount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @ordertype    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strwhere     varchar(1000) = ’’  -- 查询条件 (注意: 不要加 where)
as

declare  @strsql   varchar(1000)     -- 主语句
declare @strtmp   varchar(300)     -- 临时变量
declare @strorder varchar(400)       -- 排序类型

if @ordertype != 0
begin
    set @strtmp = "<(select min"
    set @strorder = " order by [" + @fldname +"] desc"
end
else
begin
    set @strtmp = ">(select max"
    set @strorder = " order by [" + @fldname +"] asc"
end

set @strsql = "select top " + str(@pagesize) + " * from ["
    + @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
    + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
    + @fldname + "] from [" + @tblname + "]" + @strorder + ") as tbltmp)"
    + @strorder

if @strwhere != ’’
    set @strsql = "select top " + str(@pagesize) + " * from ["
        + @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
        + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
        + @fldname + "] from [" + @tblname + "] where " + @strwhere + " "
        + @strorder + ") as tbltmp) and " + @strwhere + " " + @strorder

if @pageindex = 1
begin
    set @strtmp = ""
    if @strwhere != ’’
        set @strtmp = " where " + @strwhere 

    set @strsql = "select top " + str(@pagesize) + " * from ["
        + @tblname + "]" + @strtmp + " " + @strorder
end

if @iscount != 0
    set @strsql = "select count(*) as total from [" + @tblname + "]"

exec (@strsql)
go
--结束

当我在用这个存储过程的时候,刚开始没有发现问题,后来当我的条件很复杂的时候,发现,此存储过程执行遇到错误,下面是出现问题的条件
id<>0 and (companyenname like ’%shenzhen%’ or companychname like ’%shenzhen%’ or web like ’%shenzhen%’ or memo like ’%shenzhen%’  or address like ’%shenzhen%’)  order by [id] desc) as tbltmp) and id<>0 and (companyenname like ’%shenzhen%’ or companychname like ’%shenzhen%’ or web like ’%shenzhen%’ or memo like ’%shenzhen%’  or address like ’%shenzhen%’) and salesid=9
照说这个条件是没有问题的,可是,用上面的存储过程执行,却老是报告错误
后来,经调试,输出生成后的sql语句,发现,原来问题是出现在嵌套的sql语句中使用的()身上,于是,我把存储过程改为下面的效果,终于排除了bug,下面的存储过程不管你的条件有多复杂,只要格式正确,就能运行
create procedure getrecordfrompage
    @tblname      varchar(255),       -- 表名
    @fldname      varchar(255),       -- 字段名
    @pagesize     int = 10,           -- 页尺寸
    @pageindex    int = 1,            -- 页码
    @iscount      bit = 0,            -- 返回记录总数, 非 0 值则返回
    @ordertype    bit = 0,            -- 设置排序类型, 非 0 值则降序
    @strwhere     varchar(1000) = ’’  -- 查询条件 (注意: 不要加 where)
as

declare  @strsql   varchar(1000)     -- 主语句
declare @strtmp   varchar(300)     -- 临时变量
declare @strorder varchar(400)       -- 排序类型

if @ordertype != 0
begin
    set @strtmp = "<(select min"
    set @strorder = " order by [" + @fldname +"] desc"
end
else
begin
    set @strtmp = ">(select max"
    set @strorder = " order by [" + @fldname +"] asc"
end

set @strsql = "select top " + str(@pagesize) + " * from ["
    + @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
    + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
    + @fldname + "] from [" + @tblname + "]" + @strorder + ") as tbltmp)"
    + @strorder

if @strwhere != ’’
    set @strsql = "select top " + str(@pagesize) + " * from ["
        + @tblname + "] where [" + @fldname + "]" + @strtmp + "(["
        + @fldname + "]) from (select top " + str((@pageindex-1)*@pagesize) + " ["
        + @fldname + "] from [" + @tblname + "] where (" + @strwhere + ") "
        + @strorder + ") as tbltmp) and (" + @strwhere + ") " + @strorder

if @pageindex = 1
begin
    set @strtmp = ""
    if @strwhere != ’’
        set @strtmp = " where (" + @strwhere + ")"

    set @strsql = "select top " + str(@pagesize) + " * from ["
        + @tblname + "]" + @strtmp + " " + @strorder
end

if @iscount != 0
    set @strsql = "select count(*) as total from [" + @tblname + "]"

exec (@strsql)
go

要注意看,修改后的存储过程在使用@strwhere时,都在其前后加上了(),这样,就防止嵌套的()出现错误

下面的代码是引用该存储过程的一个范例 

sqlconnection myconnection=new sqlconnection(configurationsettings.appsettings["dsn"]);
   dataset mydataset=new dataset();
   string strkeyword=keyword.text.trim().replace("/’","/’/’");
   string strsalesid=sales.selecteditem.value;
   int recordcount=calcrecordcount();
   recordnumber.text=recordcount.tostring();
   lblrecordnumber.text=recordcount.tostring();
   string strexpress="id<>0";
   if (strkeyword!="")
    strexpress=strexpress+" and (companyenname like ’%"+strkeyword+"%’ or companychname like ’%"+strkeyword+"%’ or companyshortname like ’%"+strkeyword+"%’ or web like ’%"+strkeyword+"%’ or mainproduct like ’%"+strkeyword+"%’ or phone like ’%"+strkeyword+"%’ or memo like ’%"+strkeyword+"%’ or address like ’%"+strkeyword+"%’ or linkmanphone like ’%"+strkeyword+"%’)";
   if (strsalesid!="")
    strexpress=strexpress+" and salesid="+strsalesid;   
   sqlcommand mycommand=new sqlcommand();
   mycommand.connection=myconnection;
   mycommand.commandtext="getrecordfrompage";
   mycommand.commandtype=commandtype.storedprocedure;
   mycommand.parameters.add("@tblname","customerview");
   mycommand.parameters.add("@fldname","id");
   mycommand.parameters.add("@strwhere",strexpress);
   mycommand.parameters.add("@pagesize",int32.parse(customerlist.pagesize.tostring()));
   mycommand.parameters.add("@pageindex",int32.parse(viewstate["pageindex"].tostring())+1);
   sqldatareader myreader;
   myconnection.open();
   myreader=mycommand.executereader();   
   customerlist.virtualitemcount=recordcount;
   customerlist.datasource=myreader;
   customerlist.datakeyfield="id";
   customerlist.databind();
   myreader.close();
   myconnection.close();

在这里,要注意的是存储过程使用的pageindex变量是从1开始

  
  
 
 
  回复人： heroman1979(网络虫子) ( ) 信誉：96  2004-8-19 18:24:44  得分: 0  
 
 
   
最后,再排除一个bug
将存储过程中
declare  @strsql   varchar(1000)     -- 主语句
declare @strtmp   varchar(300)     -- 临时变量
declare @strorder varchar(400)       -- 排序类型
改为
declare  @strsql   varchar(2000)     -- 主语句
declare @strtmp   varchar(1000)     -- 临时变量
declare @strorder varchar(1000)       -- 排序类型

因为我在调试中发现有些查询用的语句选不止300
最后,只要将以上存储过程代码复制并加入到你的数据库中,再按照范例来调用即可了