T-SQL - Using CASE with Parameters in WHERE clause

Question

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 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 CASE here smells bad but I'm not sure how to correct it. Can you please assist?

Thanks.

5arx

Answer 1

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