asp.net mssql 多條件查詢以及分頁預存程序

create procedure [dbo].searchbykey

@iskeyword varchar(100) ,
@iskf  varchar(100),
@isdy varchar(100) ,
@iszj varchar(100),
@lr_begin varchar(100),
@lr_end varchar(100),
@iszt varchar(50),
@islx varchar(100),
@isly varchar(100),
@pagesize   int,     -- 頁尺寸 
@pageindex  int     -- 頁碼 
as 
declare

@sqlsel   varchar(1000),
@strsql varchar(1000)

set @sqlsel=+'select * from sickinfo where 1=1 '

                if (@iskeyword !='')
              set   @sqlsel = isnull(@sqlsel,'')+'and askedinfo like'''+ '%'+@iskeyword+'%'''

                if (@iskf != '')
                  set  @sqlsel=isnull(@sqlsel,'')+ 'and kefu='''+@iskf+''''
                if (@isdy !='')
                  set  @sqlsel =isnull(@sqlsel,'')+' and admit=''' + @isdy+''''
                if (@iszj !='')
                  set  @sqlsel =isnull(@sqlsel,'')+' and zhuanjia=''' + @iszj+''''
                if (@lr_begin !='' and @lr_end !='')

                set @sqlsel = @sqlsel+' and addtime between '''+convert(char(10),convert(datetime,@lr_begin),21) +''' and '''+convert(char(10),convert(datetime,@lr_end),21)+''''

                if (@iszt != '')           
                  set   @sqlsel=isnull(@sqlsel,'')+'and state =''' +@iszt+''''
                if (@islx !='')
                   set  @sqlsel=isnull(@sqlsel,'')+'and istype='''+@islx+'''' 

                if (@isly != '')
                 set   @sqlsel =isnull(@sqlsel,'')+'and infofrom ='''+@isly+''''

--按順序排列
 --set @strsql ='select top ' +str(@pagesize) +' * from ('+@sqlsel+') as t1 where id>(select isnull(max(id),0) from(select top '+ str((@pageindex-1)*@pagesize) +' id from ('+@sqlsel+') as t2 order by id  )as t)order by id '
-- 倒敘排列

if(@pageindex =1)
begin
set @strsql='select top '+str(@pagesize)+' * from ('+@sqlsel+') as tt order by id desc '
--set @strsql='select top '+str(@pagesize)+' * from #temp2 order by id desc '

end
else
begin
 set @strsql ='select top ' +str(@pagesize) +' * from ('+@sqlsel+') as t1 where id<(select isnull(min(id),0) from(select top '+str((@pageindex-1)*@pagesize) +' id from ('+@sqlsel+') as t2 order by id desc )as t)order by id desc '
end
exec (@strsql)
--exec @strsql
go

調用此預存程序的函數

   

  public dataset searchbykey(string strsql, string iskeyword, string iskf, string isdy, string iszj, string lr_begin, string lr_end, string iszt, string islx, string isly,int pagesize,int pageindex)
        {
            checkconnection();
            dataset ds1 = new dataset();
            try
            {
                cmd = new sqlcommand(strsql, con);
                cmd.commandtype = commandtype.storedprocedure;
                sqlparameter[] para = { new sqlparameter("@iskeyword", sqldbtype.varchar),
                                      new sqlparameter("@iskf", sqldbtype.varchar),
                                      new sqlparameter("@isdy",sqldbtype.varchar),
                                      new sqlparameter("@iszj",sqldbtype.varchar),
                                      new sqlparameter("@lr_begin",sqldbtype.varchar),
                                      new sqlparameter("@lr_end",sqldbtype.varchar),
                                      new sqlparameter("@iszt",sqldbtype.varchar),
                                      new sqlparameter("@islx",sqldbtype.varchar),
                                      new sqlparameter("@isly",sqldbtype.varchar),
                                      new sqlparameter("@pagesize",sqldbtype.varchar),
                                      new sqlparameter("@pageindex",sqldbtype.varchar)};
                para[0].value = iskeyword;
                para[1].value = iskf;
                para[2].value = isdy;
                para[3].value = iszj;
                para[4].value = lr_begin;
                para[5].value = lr_end;
                para[6].value = iszt;
                para[7].value = islx;
                para[8].value = isly;
                para[9].value = pagesize;
                para[10].value = pageindex;
                cmd.parameters.addrange(para);
                sqldataadapter dap = new sqldataadapter();
                dap.selectcommand = cmd;
                dap.fill(ds1, "sickinfo");
            }
            catch (exception e)
            {
                e.message.tostring();
            }
            finally
            {
                con.close();
            }
            return ds1;
        }