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