存储过程里多条件判断(SQL组合查询)

存储过程里多条件判断(SQL组合查询)

https://www.cnblogs.com/jhxk/articles/4844221.html

我存储过程里想实现多个传入参数的判断,里面有7个传入参数条件.
CREATE PROCEDURE sp_tbWasteSource_Search
(
    @sd   datetime,           //开始日期
    @ed   datetime,           //结束日期
    @con1 varchar(50),        
    @con2 varchar(30),        
    @con3 varchar(5),        
    @con4  varchar(10),       
    @con5 varchar(4)         
)
AS
declare @sql varchar(1000)
begin
  set @sql='SELECT * FROM table '
  if  @sd!='' and @ed!=''
    begin
      set @sql=@sql+' where CosID= '+cast(@CosID as varchar)
    end
  if (@CosName!= '' and @CosID!='')
    begin
      set @sql=@sql+'and'+'  CosName=  '+cast (@CosName as varchar )
    end
  else 
    if (@CosName!='' and @CosID ='')
      begin
        set @sql=@sql+'where '+'  CosName=  '+cast (@CosName as varchar )
      end
  if @CosCredit!= '' and (@CosID!='' or @CosName!='')
    begin
      set @sql=@sql+' and  CosCredit=  '+cast (@CosCredit as varchar )
    end
  else 
    if @CosCredit!='' and @CosID=''and @CosName=''
      set @sql=@sql+'where CosCredit= '+cast (@CosCredit as varchar )
  exec (@sql)
end
GO

无论是ADO.NET还是存储过程的组合查询的SQL语句编写方式:select * from temp where (@ServerID='' or ServerID=@ServerID) and (@SName='' or SName=@SName)

SqlParameter param=new SqlParameter("@ServerID",ServerID==-1?String.Empty:ServerID.ToString());//这里的ServerID传递过来是int类型,如果为-1,那么查询全部,传递空串即可,否则传递ServerID,数据库会自动将@ServerID的值转换为int

SqlParameter param=new SqlParameter("@SName",SName); //字符串直接传参数即可，因为可以为空串String.Empty

以下为参考:

CREATE PROCEDURE sp_tbWasteSource_Search
(
@sd datetime=null, //开始日期
@ed datetime=null, //结束日期
@con1 varchar(50), 
@con2 varchar(30), 
@con3 varchar(5), 
@con4 varchar(10), 
@con5 varchar(4) 
)
as 
begin
select * from tb 
where (@sd is null or date>@sd) and (@ed is null or date<@ed)
end

 

SELECT * FROM table_name
where ((@sd is null and @ed is null) or (sd >= @sd and ed <= @ed)) --sd和ed都是空时,此条件总为true,否则相当于sd >= @sd and ed <= @ed
and (@con1 is null or con1 = @con1) --如果@con1不传值,则此行条件总为true;如果传值,则此行为and con1=@con1
and (@con2 is null or con2 = @con2) --同con1
and (@con3 is null or con3 = @con3) --同con1
and (@con4 is null or con4 = @con4) --同con1
and (@con5 is null or con5 = @con5) --同con1

 

SELECT * FROM table
where 1=1
and sd>= case when @sd is not null when @sd else sd end
and ed<= case when @ed is not null when @ed else ed end
and CosID= case when @CosID is not null when @CosID else CosID end
and CosName= case when @CosName is not null when @CosName else CosName end
and .....