SQLServer 存储过程中不拼接SQL字符串实现多条件查询

发布时间：2020-12-12 15:03:05 所属栏目：MsSql教程来源：网络整理

导读：以前拼接的写法 ? ? 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

以前拼接的写法 ?? set@sql=' select * from table where 1=1 '?? if (@addDate isnot null)? ?? ?????set@sql = @sql+' and? addDate = '+ @addDate + '? '? if (@name<>''and is notnull)? ????set@sql = @sql+ ' and name = '+ @name+ ' 'exec(@sql) ?? 下面是不采用拼接SQL字符串实现多条件查询的解决方案 ?? 第一种写法是感觉代码有些冗余 if (@addDate isnot null) and(@name<> '')? ??????select* fromtablewhereaddDate = @addDate andname= @name? elseif (@addDate isnot null) and(@name='')? ??????select* fromtablewhereaddDate = @addDate? elseif(@addDate is? null) and (@name<> '')? ??????select* fromtablewhereand name = @name? elseif(@addDate is? null) and (@name= '')? select* fromtable? ?? 第二种写法是? ?? select* fromtablewhere(addDate = @addDate or@addDate isnull) and (name= @nameor @name= '')? 第三种写法是 ?? SELECT* FROMtablewhere? addDate = CASE@addDate ISNULL THEN addDate ELSE@addDate END,? name= CASE@nameWHEN'' THEN name ELSE @nameEND

地址:http://www.cnblogs.com/xiaofengfeng/archive/2010/11/09/1873106.html

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!