在這里,感謝所有csdn blog上貼過(guò)相關(guān)主題貼子的兄弟,下面的方法是建立在csdn blog上七篇關(guān)于分頁(yè)存儲(chǔ)過(guò)程的基礎(chǔ)上的

前段時(shí)間研究分頁(yè)的時(shí)候,在csdn的blog上看到了一位兄弟寫(xiě)的分頁(yè)存儲(chǔ)過(guò)程,發(fā)現(xiàn)非常好,于是,就使用了這個(gè)存儲(chǔ)過(guò)程,下面是原版的分頁(yè)存儲(chǔ)過(guò)程

--開(kāi)始
create procedure getrecordfrompage
    @tblname      varchar(255),       -- 表名
    @fldname      varchar(255),       -- 字段名
    @pagesize     int = 10,           -- 頁(yè)尺寸
    @pageindex    int = 1,            -- 頁(yè)碼
    @iscount      bit = 0,            -- 返回記錄總數(shù), 非 0 值則返回
    @ordertype    bit = 0,            -- 設(shè)置排序類型, 非 0 值則降序
    @strwhere     varchar(1000) = ’’  -- 查詢條件 (注意: 不要加 where)
as

declare  @strsql   varchar(1000)     -- 主語(yǔ)句
declare @strtmp   varchar(300)     -- 臨時(shí)變量
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
--結(jié)束

當(dāng)我在用這個(gè)存儲(chǔ)過(guò)程的時(shí)候,剛開(kāi)始沒(méi)有發(fā)現(xiàn)問(wèn)題,后來(lái)當(dāng)我的條件很復(fù)雜的時(shí)候,發(fā)現(xiàn),此存儲(chǔ)過(guò)程執(zhí)行遇到錯(cuò)誤,下面是出現(xiàn)問(wèn)題的條件
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
照說(shuō)這個(gè)條件是沒(méi)有問(wèn)題的,可是,用上面的存儲(chǔ)過(guò)程執(zhí)行,卻老是報(bào)告錯(cuò)誤
后來(lái),經(jīng)調(diào)試,輸出生成后的sql語(yǔ)句,發(fā)現(xiàn),原來(lái)問(wèn)題是出現(xiàn)在嵌套的sql語(yǔ)句中使用的()身上,于是,我把存儲(chǔ)過(guò)程改為下面的效果,終于排除了bug,下面的存儲(chǔ)過(guò)程不管你的條件有多復(fù)雜,只要格式正確,就能運(yùn)行
create procedure getrecordfrompage
    @tblname      varchar(255),       -- 表名
    @fldname      varchar(255),       -- 字段名
    @pagesize     int = 10,           -- 頁(yè)尺寸
    @pageindex    int = 1,            -- 頁(yè)碼
    @iscount      bit = 0,            -- 返回記錄總數(shù), 非 0 值則返回
    @ordertype    bit = 0,            -- 設(shè)置排序類型, 非 0 值則降序
    @strwhere     varchar(1000) = ’’  -- 查詢條件 (注意: 不要加 where)
as

declare  @strsql   varchar(1000)     -- 主語(yǔ)句
declare @strtmp   varchar(300)     -- 臨時(shí)變量
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

要注意看,修改后的存儲(chǔ)過(guò)程在使用@strwhere時(shí),都在其前后加上了(),這樣,就防止嵌套的()出現(xiàn)錯(cuò)誤

下面的代碼是引用該存儲(chǔ)過(guò)程的一個(gè)范例 

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();

在這里,要注意的是存儲(chǔ)過(guò)程使用的pageindex變量是從1開(kāi)始

  
  
 
 
  回復(fù)人： heroman1979(網(wǎng)絡(luò)蟲(chóng)子) ( ) 信譽(yù)：96  2004-8-19 18:24:44  得分: 0  
 
 
   
最后,再排除一個(gè)bug
將存儲(chǔ)過(guò)程中
declare  @strsql   varchar(1000)     -- 主語(yǔ)句
declare @strtmp   varchar(300)     -- 臨時(shí)變量
declare @strorder varchar(400)       -- 排序類型
改為
declare  @strsql   varchar(2000)     -- 主語(yǔ)句
declare @strtmp   varchar(1000)     -- 臨時(shí)變量
declare @strorder varchar(1000)       -- 排序類型

因?yàn)槲以谡{(diào)試中發(fā)現(xiàn)有些查詢用的語(yǔ)句選不止300
最后,只要將以上存儲(chǔ)過(guò)程代碼復(fù)制并加入到你的數(shù)據(jù)庫(kù)中,再按照范例來(lái)調(diào)用即可了