多条件查询以及分页存储过程（倒叙和顺序查询）

废话少说直接贴上代码

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;
        }

转载于:https://www.cnblogs.com/xiaogelove/archive/2010/12/27/1918088.html