T-SQL - Using CASE with Parameters in WHERE clause

I'm running a report on a Sales table:

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, @BrandID and two DateTime 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 SalesUserI开发者_如何转开发D 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 CASE here smells bad but I'm not sure how to correct it. Can you please assist?

Thanks.

5arx

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

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)
   )    

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

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.

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

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

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

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

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

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

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

Try this:

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