拼接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
