废话少说直接贴上代码
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;
}