ID #64922

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


2011-08-24 19:42
阅读:
I'm VC , Just U know Y
本站部分文章来源于互联网,版权归原作者所有。

延伸阅读:

SQL Server 数据存储与 NTFS 簇的大小

SQL 2005 发送邮件 存储过程

Sql server数据库密码安全追踪和存储

SQLserver 2000分页另类通用解决方案

SQL Server存储过程命名方法