拼接SQL字符串实现多条件查询，以前拼接的写法
set @sql=" select * from table where 1=1 "
if (@addDate is not null)
set @sql = @sql+" and addDate = "+ @addDate + " "
if (@name <>"" and is not null)
set @sql = @sql+ " and name = " + @name + " "
exec(@sql)
下面是 不采用拼接SQL字符串实现多条件查询的解决方案
第一种写法是 感觉代码有些冗余
if (@addDate is not null) and (@name <> "")
select * from table where addDate = @addDate and name = @name
else if (@addDate is not null) and (@name ="")
select * from table where addDate = @addDate
else if(@addDate is null) and (@name <> "")
select * from table where and name = @name
else if(@addDate is null) and (@name = "")
select * from table
第二种写法是
select * from table where (addDate = @addDate or @addDate is null) and (name = @name or @name = "")
第三种写法是
SELECT * FROM table where
addDate = CASE @addDate IS NULL THEN addDate ELSE @addDate END,
name = CASE @name WHEN "" THEN name ELSE @name END
