用于获取记录的条件 WHERE 子句 [英] Conditional WHERE clause for fetching records

问题描述

我使用的是 SQL Server 2008 R2.

I am using SQL Server 2008 R2.

我有一个 SQL 块，如下所示:

I am having an SQL block as below :

DECLARE @Day INT = 5
DECLARE @Month INT = NULL
DECLARE @year INT = NULL
DECLARE @dtnow DATETIME

SET @dtnow = GETDATE()

IF @Month IS NULL
    SELECT @Month = MONTH(DATEADD(MONTH,-1, @dtnow ))

IF @Year IS NULL
    SELECT @year = YEAR(DATEADD(MONTH,-1, @dtnow ))


SELECT * 
FROM TblSalesRecords 
WHERE 
    CASE WHEN @Day IS NULL THEN -- Condition One
        BEGIN 
            ISNULL(@Month, MONTH(TblSalesRecords.CreatedDate)) = MONTH(TblSalesRecords.CreatedDate) 
            AND (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate)
        END 
    ELSE                        -- Condition two
        BEGIN 
            TblSalesRecords.CreatedDate BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(MONTH,-1, @dtnow ))) + '-' + CONVERT(VARCHAR,month(DATEADD(MONTH,-1,@dtnow))) + '-' + CONVERT(VARCHAR, DAY(DATEADD(MONTH,-1,@dtnow))) +' 00:00:00.000') 
            AND CONVERT (DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(DAY,-1,@dtnow ))) + '-' + CONVERT(VARCHAR,MONTH(DATEADD(DAY,-1,@dtnow))) + '-' + CONVERT(VARCHAR, DAY(DATEADD(DAY,-1,@dtnow))) +' 23:59:59.998')
        END
    END 
    AND TblSalesRecords.IsDeleted=0

我想要实现的是，

如果@Day被设置为NULL，那么它应该执行的CASE..WHEN语句——条件一 else of ——条件二.

If @Day is set to NULL then it should execute the CASE..WHEN Statement of -- Condition One else of -- Condition two.

但我在 CASE..WHEN 语句中的 "=" 附近出现语法错误.

But I am getting Syntax error near "=" in CASE..WHEN Statement.

谁能告诉我如何实现这一目标?

Can anyone let me know how to achieve this?

推荐答案

你可以这样实现:

SELECT * 
FROM TblSalesRecords 
WHERE (@Day IS NULL 
            AND ISNULL(@Month, MONTH(TblSalesRecords.CreatedDate)) = MONTH(TblSalesRecords.CreatedDate) 
            AND (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate)
       )
    OR (@Day IS NOT NULL                      -- Condition two
            AND TblSalesRecords.CreatedDate BETWEEN CONVERT(DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(MONTH,-1, @dtnow ))) + '-' + CONVERT(VARCHAR,month(DATEADD(MONTH,-1,@dtnow))) + '-' + CONVERT(VARCHAR, DAY(DATEADD(MONTH,-1,@dtnow))) +' 00:00:00.000') 
            AND CONVERT (DATETIME, CONVERT(VARCHAR,YEAR(DATEADD(DAY,-1,@dtnow ))) + '-' + CONVERT(VARCHAR,MONTH(DATEADD(DAY,-1,@dtnow))) + '-' + CONVERT(VARCHAR, DAY(DATEADD(DAY,-1,@dtnow))) +' 23:59:59.998')
        )
    AND TblSalesRecords.IsDeleted=0

(@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate) 中还有另一个语法错误，但我不确定那里应该有什么，所以我保持原样.

There's another syntax error in (@year, YEAR(TblSalesRecords.CreatedDate)) = YEAR(TblSalesRecords.CreatedDate) but I'm not sure what should be there so I left it as it is.

这篇关于用于获取记录的条件 WHERE 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！