T-SQL - 在 WHERE 子句中使用带参数的 CASE

T-SQL - Using CASE with Parameters in WHERE clause

提问by indra

I'm running a report on a Salestable:

我正在一张Sales桌子上运行报告：

SaleId INT | SalesUserID INT | SiteID INT | BrandId INT| SaleDate DATETIME

SaleId INT | SalesUserID INT | SiteID INT | BrandId INT| SaleDate DATETIME

I'm having a nightmare trying to do something like this with a set of Nullable parameters @SalesUserID, @SiteId, @BrandIDand two DateTime params.

我有一个噩梦试图做这样的事情了一套可空参数@SalesUserID，@SiteId，@BrandID和两个日期时间PARAMS。

Additional Point: Only ONE of the filter parameters will ever be passed as a Non-Null value.

附加点：只有一个过滤器参数将作为非空值传递。

SELECT * from Sales
WHERE
     SaleDate BETWEEN @StartDate AND @EndDate

AND
    SalesUserID IN
(
    Select SalesUserID FROM Sales
    WHERE
        SaleDate BETWEEN @StartDate AND @EndDate

    AND

        CASE
            WHEN @SalesUserId IS NOT NULL THEN SalesUserId = @SalesUserID
            WHEN @SiteId Is Not Null THEN SiteId = @SiteId
            ELSE BrandId = @BrandID
        END

)

My use of CASEhere smells bad but I'm not sure how to correct it. Can you please assist?

我在CASE这里的使用闻起来很糟糕，但我不知道如何纠正它。你能帮忙吗？

Thanks.

谢谢。

5arx

5arx

回答by Fosco

I don't think you want a CASE statement at all, but a compound conditional... Give this a shot and let me know:

我认为您根本不需要 CASE 语句，而是需要复合条件……试一试，让我知道：

select * 
from Sales
where SaleDate between @StartDate and @EndDate
and
   (
    (@SalesUserId is not null and SalesUserId = @SalesUserID)
    or (@SiteId is not null and SiteId = @SiteId)
    or (BrandId = @BrandID)
   )    

回答by Tomalak

If I understood you correctly, you want the three conditions either be NULL or checked for:

如果我理解正确，您希望三个条件为 NULL 或检查：

WHERE
    /* ... */
    AND SalesUserId = ISNULL(@SalesUserId, SalesUserId)
    AND SiteId      = ISNULL(@SiteId, SiteId)
    AND BrandId     = ISNULL(@BrandID, BrandID)

Be aware that this forces a table scan which might not be in your best interest.

请注意，这会强制执行可能不符合您最佳利益的表扫描。

回答by Cameron

This should work and use any index if you want to use CASE:

如果您想使用，这应该可以工作并使用任何索引CASE：

SELECT * 
from Sales 
WHERE SaleDate BETWEEN @StartDate AND @EndDate 
AND SalesUserID = CASE WHEN @SalesUserID IS NULL THEN 
SalesUserID ELSE @SalesUserID END

回答by Maziar Taheri

I would use a dynamic generated code in such a circumstance:

在这种情况下，我会使用动态生成的代码：

declare @SalesUserId int,@SiteId int,@StartDate datetime, @EndDate datetime,@BrandID int
declare @sql nvarchar(max)

    set @sql = N'
    SELECT * from Sales
    WHERE
         SaleDate BETWEEN @StartDate AND @EndDate

    AND
        SalesUserID IN
    (
        Select SalesUserID FROM Sales
        WHERE
            SaleDate BETWEEN @StartDate AND @EndDate AND
    ' +
            CASE
                WHEN @SalesUserId IS NOT NULL THEN 'SalesUserId = @SalesUserID'
                WHEN @SiteId Is Not Null THEN 'SiteId = @SiteId'
                ELSE 'BrandId = @BrandID'
            END

    +')'

    print @sql

    exec sp_executesql @sql
        , N'@SalesUserId int,
            @SiteId int,
            @StartDate datetime,
            @EndDate datetime,
            @BrandID int'
            ,@SalesUserId
            ,@SiteId
            ,@StartDate
            ,@EndDate
            ,@BrandID

回答by Alex K.

COALESCE()returns the 1st non NULL argument so you could;

COALESCE()返回第一个非 NULL 参数，以便您可以；

...
WHERE SaleDate BETWEEN @StartDate AND @EndDate
  AND SalesUserId = COALESCE(@SalesUserId, SalesUserId)
  AND SiteId = COALESCE(@SiteId, SiteId)
  AND BrandID = COALESCE(@BrandID, BrandId)

回答by HLGEM

This is generally a job for dynamic SQl if you want performance.

如果您想要性能，这通常是动态 SQL 的工作。

http://www.sommarskog.se/dynamic_sql.html

http://www.sommarskog.se/dynamic_sql.html

回答by CLAU

Try this:

尝试这个：

SELECT * 
from Sales 
WHERE SaleDate BETWEEN @StartDate AND @EndDate 
AND SalesUserID = CASE WHEN @SalesUserID IS NULL THEN 
SalesUserID ELSE @SalesUserID END