基于 SQL 条件的搜索，不适用于 AND 条件

本文介绍了基于 SQL 条件的搜索，不适用于 AND 条件的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我正在执行搜索操作(客户端通过存储过程搜索)我有一个Nvarchar"类型的列ArticleHeader"和一个日期"列DateEffective"我通过传递@operator 来执行基于条件的搜索，对于空闲片段，我试图在连接(AND)中搜索日期和文章标题如果我单独运行它们，我不会得到任何结果.

找到片段:

创建表#TempItems(日期生效日期，文章标题 NVARCHAR(50))插入 #TempItems 值 ('2019-12-28','Nieuws')插入 #TempItems 值 ('2020-02-12','Test')插入 #TempItems 值 ('2020-01-10','zolo')插入 #TempItems 值 ('2020-02-23','valued')声明@DateEffective 日期时间，@operator nvarchar(10)--set @DateEffective ='2020-01-10'设置@operator='neq'声明@ArticleHeader nvarchar (50) ='Nieuws'从 #TempItems PA 中选择 *其中 1=1--------------------------------------------------------------------和(((@operator='neq') 和当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，PA.DateEffective)结束！=当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，@DateEffective)结尾)或者((@operator='eq') 和当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，PA.DateEffective)结束 =当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，@DateEffective)结尾))----------------------------------------------------------------------和((lower(@Operator) = 'eq' AND当@ArticleHeader 为空时的情况然后'1'ELSE PA.ArticleHeader结束 =当@ArticleHeader 为空时的情况然后'1'其他转换(nvarchar(1000)，@ArticleHeader)结尾)或者(lower(@Operator) = 'startswith' AND当@ArticleHeader 为空时的情况然后'1'ELSE PA.ArticleHeader结束喜欢当@ArticleHeader 为空时的情况然后'1'ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%'结尾))---------------------------------------------------------------------------

解决方案

嗨@Azhar 能否请您检查一下这个查询，它在两种情况下都正常工作.

DROP TABLE IF EXISTS #TempItems创建表#TempItems(日期生效日期，文章标题 NVARCHAR(50))插入 #TempItems 值 ('2019-12-28','Nieuws')插入 #TempItems 值 ('2020-02-12','Test')插入 #TempItems 值 ('2020-01-10','zolo')插入 #TempItems 值 ('2020-02-23','valued')插入 #TempItems 值 ('2020-02-24','Nieuws')声明@DateEffective 日期时间，@operator nvarchar(10)设置@DateEffective ='2020-01-10'设置@operator='startswith'声明@ArticleHeader nvarchar (50) ='Nieuws'从 #TempItems PA 中选择 *其中 1=1--------------------------------------------------------------------和(((@operator='neq') 和当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，PA.DateEffective)结束！=当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，@DateEffective)结尾)或者((@operator='eq') 和当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，PA.DateEffective)结束 =当@DateEffective 为空时的情况然后'1900-1-1'其他转换(日期，@DateEffective)结尾)或者(1=1))-----------------------------------------------------------------和((lower(@Operator) = 'eq' AND当@ArticleHeader 为空时的情况然后'1'ELSE PA.ArticleHeader结束 =当@ArticleHeader 为空时的情况然后'1'其他转换(nvarchar(1000)，@ArticleHeader)结尾)或者(lower(@Operator) = 'startswith' AND当@ArticleHeader 为空时的情况然后'1'ELSE PA.ArticleHeader结束喜欢当@ArticleHeader 为空时的情况然后'1'ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%'结尾)或者(lower(@Operator) = 'neq' AND当@ArticleHeader 为空时的情况然后'1'ELSE PA.ArticleHeader结束 <>当@ArticleHeader 为空时的情况然后'1'其他转换(nvarchar(1000)，@ArticleHeader)结尾))---------------------------------------------------------------------------

输出

开始

I am implementing search operation (client side searching via stored procedure) i have a 'Nvarchar' type column 'ArticleHeader' and a 'DATE' column 'DateEffective' i am performing conditional based searching by passing @operator, for the fallowing snippet i am trying to search date and article header in Conjunction (AND) i am getting no results how ever if i run them individually they works out fine.

Find the snippet :

CREATE TABLE #TempItems ( DateEffective DATE, ArticleHeader NVARCHAR(50) ) insert into #TempItems values ('2019-12-28','Nieuws') insert into #TempItems values ('2020-02-12','Test') insert into #TempItems values ('2020-01-10','zolo') insert into #TempItems values ('2020-02-23','valued') declare @DateEffective datetime,@operator nvarchar(10) --set @DateEffective ='2020-01-10' set @operator='neq' declare @ArticleHeader nvarchar (50) ='Nieuws' select * from #TempItems PA Where 1=1 -------------------------------------------------------------------- And ( ( (@operator='neq') And CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE convert(date,PA.DateEffective) END != CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE Convert(Date, @DateEffective) End ) OR ( (@operator='eq') And CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE convert(date,PA.DateEffective) END = CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE Convert(Date, @DateEffective) End ) ) ---------------------------------------------------------------------- AND ( (lower(@Operator) = 'eq' AND CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE PA.ArticleHeader END = CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE CONVERT(nvarchar(1000), @ArticleHeader) END ) OR (lower(@Operator) = 'startswith' AND CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE PA.ArticleHeader END Like CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%' END ) ) ---------------------------------------------------------------------------

解决方案

Hi @Azhar can you please check this query it's working fine in both cases.

DROP TABLE IF EXISTS #TempItems CREATE TABLE #TempItems ( DateEffective DATE, ArticleHeader NVARCHAR(50) ) insert into #TempItems values ('2019-12-28','Nieuws') insert into #TempItems values ('2020-02-12','Test') insert into #TempItems values ('2020-01-10','zolo') insert into #TempItems values ('2020-02-23','valued') insert into #TempItems values ('2020-02-24','Nieuws') declare @DateEffective datetime,@operator nvarchar(10) set @DateEffective ='2020-01-10' set @operator='startswith' declare @ArticleHeader nvarchar (50) ='Nieuws' select * from #TempItems PA Where 1=1 -------------------------------------------------------------------- And ( ( (@operator='neq') And CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE convert(date,PA.DateEffective) END != CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE Convert(Date, @DateEffective) End ) OR ( (@operator='eq') And CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE convert(date,PA.DateEffective) END = CASE WHEN @DateEffective IS NULL THEN '1900-1-1' ELSE Convert(Date, @DateEffective) End ) OR ( 1=1 ) ) ------------------------------------------------------------------------ AND ( (lower(@Operator) = 'eq' AND CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE PA.ArticleHeader END = CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE CONVERT(nvarchar(1000), @ArticleHeader) END ) OR (lower(@Operator) = 'startswith' AND CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE PA.ArticleHeader END Like CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE CONVERT(nvarchar(1000), @ArticleHeader) + '%' END ) OR (lower(@Operator) = 'neq' AND CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE PA.ArticleHeader END <> CASE WHEN @ArticleHeader IS NULL THEN '1' ELSE CONVERT(nvarchar(1000), @ArticleHeader) END ) ) ---------------------------------------------------------------------------

Output

Startwith