以前拼接的写法 |
|
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 |