SQLServer存储过程中实现多条件查询

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