时间戳

首页 > 解决方案 > SQL Server：'<' 和 'day' 附近的语法不正确，期望 '(' 或 SELECT

sql - SQL Server：'<' 和 'day' 附近的语法不正确，期望 '(' 或 SELECT

问题描述

以下是将查询返回到我的暗网项目的过程。我想在哪里获取过去 6 个月、过去 1 周和过去 1 个月的记录。

我会根据查询结果返回'KAYITTARIHI'

ALTER PROCEDURE [dbo].[CVSearch]
    @Adi nvarchar(50) = NULL,
    @Soyadi nvarchar(50) = NULL,
    @BasvuruTarihi nvarchar(50) = NULL
AS
    IF (@Adi = '')
        SET @Adi = NULL

    IF (@Soyadi = '')
        SET @Soyadi = NULL

    IF (@BasvuruTarihi = '')
        SET @BasvuruTarihi = NULL
            
    SELECT ID_CV 
    FROM CV 
    WHERE KULLANICILAR_CV_SILMEDURUMU  = 0 
      AND ADI LIKE '%' + ISNULL(@Adi, ADI) + '%' 
      AND SOYADI LIKE '%' + ISNULL(@Soyadi, SOYADI) + '%' 
      AND CASE 
             WHEN @BasvuruTarihi = 'Hepsi' 
                THEN KAYITTARIHI  
             WHEN @BasvuruTarihi = 'Last 1 week' 
                THEN DATEDIFF(day, KAYITTARIHI, GETDATE()) < 8 
             WHEN @BasvuruTarihi = 'Last 1 month' 
                THEN DATEDIFF(day, KAYITTARIHI, GETDATE()) < 31  
             WHEN @BasvuruTarihi = 'Last 6 month' 
                THEN DATEDIFF(day, KAYITTARIHI, GETDATE()) < 180 
             ELSE @BasvuruTarihi 
          END = KAYITTARIHI

但我收到这些错误：

当 @BasvuruTarihi='Last 1 week' 然后 DATEDIFF(day,KAYITTARIHI,GETDATE())<8 时，'<' =>> 附近的语法不正确

当 @BasvuruTarihi='Last 1 month' THEN DATEDIFF(day,KAYITTARIHI,GETDATE()) <31 时，'day' 附近的语法不正确预期 '(' 或 SELECT =>>

'=' 附近的语法不正确 期待对话 =>> end = KAYITTARIHI

标签： sqlsql-servertsqlcase

解决方案

这是一个完全盲目的猜测，但我猜这是你真正想要的东西。由于领先的外卡，这仍然不会表现出色，但是，我至少已经尽可能多地做到了 SARGable。我还添加OPTION (RECOMPILE)了为数据引擎提供使用与查询相关的查询计划的最佳机会。

SELECT ID_CV
FROM dbo.CV
WHERE KULLANICILAR_CV_SILMEDURUMU = 0
  AND (ADI LIKE N'%' + @Adi + N'%' OR @Adi IS NULL)
  AND (SOYADI LIKE N'%' + @Soyadi + N'%' OR @Soyadi IS NULL)
  AND (@BasvuruTarihi = N'Hepsi'
   OR  (@BasvuruTarihi = N'Last 1 week' AND KAYITTARIHI >= CONVERT(date,DATEADD(DAY,-7,GETDATE())))
   OR  (@BasvuruTarihi = N'Last 1 week' AND KAYITTARIHI >= CONVERT(date,DATEADD(DAY,-30,GETDATE()))) --This isn't a month
   OR  (@BasvuruTarihi = N'Last 1 week' AND KAYITTARIHI >= CONVERT(date,DATEADD(DAY,-180,GETDATE())))) --This isn't 6 months
OPTION (RECOMPILE);

推荐阅读