基于 SQL 条件的搜索，不适用于 AND 条件 [英] SQL Condition based searching ,not working with AND condition

问题描述

我正在执行搜索操作(客户端通过存储过程搜索)我有一个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

这篇关于基于 SQL 条件的搜索，不适用于 AND 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！