网站地图
【www.gdgbn.com--word】
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;
}