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;
}
补充:asp.net教程,.Net开发