Geek Answers Handbook

T-SQL - using CASE with parameters in a WHERE clause

I run the report in the Sales table:

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

I have a nightmare trying to do something similar with a set of Nullable @SalesUserID , @SiteId , @BrandID and two DateTime parameters.

Additional point: only ONE of the filter parameters will ever be passed as a value other than Null.

 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 fix it. Can you help?

Thanks.

5arx

+4
source share
7 answers

I don't think you need a CASE statement at all, but a complicated conditional ... Give me a snapshot 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) )
+8
source

If you understand correctly, you want the three conditions to be either NULL or marked:

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

Remember that this forces a table scan, which may not be in your interest.

+5
source

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
+3
source

COALESCE() returns the 1st argument is not NULL so you can;

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

This is usually a task for dynamic SQl if you want performance.

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

+1
source

I would use dynamically generated code in this case:

 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
+1
source

Try the following:

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

More articles:


All Articles