CODEWITHSUNDEEP
sql-server-2008t-sqlcasewhere-clause
crazy novice
crazy novice

Reputation: 1817

using a CASE within WHERE

I have code where I need to add filter into where clause that varies based on value of variable. As in example below if @X has a value of 0 then I want to include a filter for OrderID = 10; otherwise I want to add filter for OrderID = 20 with another filter for DepartmentID either been NULL or a value of 30. This could be accomplished using a IF ELSE as below

DECLARE @X INT

-- Retrieve value for @X

IF @X = 0 
BEGIN
    SELECT *
    FROM Customers
    WHERE ProductID IS NOT NULL
    AND OrderID = 10
END
ELSE
BEGIN
    SELECT *
    FROM Customers
    WHERE ProductID IS NOT NULL
    AND OrderID = 20 AND ( DepartmentID IS NULL OR DepartmentID = 30)
END

I wonder if there is someway of doing it using one SQL statement. I thought it's doable using CASE within a WHERE but SQL does not seem to allow below.

SELECT *
    FROM Customers
    WHERE ProductID IS NOT NULL
    AND CASE WHEN @X = 0 THEN OrderID = 10 ELSE OrderID = 20 AND ( DepartmentID IS NULL OR DepartmentID = 30) END

Is there anyway of accomplishing this.

Upvotes: 2

Views: 315

Answers (3)

Guffa
Guffa

Reputation: 700232

You can do that without a CASE:

SELECT *
FROM Customers
WHERE ProductID IS NOT NULL
AND ((@X = 0 AND OrderID = 10) OR (@X <> 0 AND OrderID = 20 AND DepartmentID = 30))

Edit:

With the condition that you wanted to add:

SELECT *
FROM Customers
WHERE ProductID IS NOT NULL
AND ((@X = 0 AND OrderID = 10) OR (@X <> 0 AND OrderID = 20 AND (DepartmentID IS NULL OR DepartmentID = 30)))

Upvotes: 1

Giannis Paraskevopoulos
Giannis Paraskevopoulos

Reputation: 18411

SELECT *
FROM Customers
WHERE ProductID IS NOT NULL
AND OrderID = CASE WHEN @X = 0 THEN 10 ELSE 20 END
AND ISNULL(DepartmentID,30) = CASE WHEN @X = 0 THEN ISNULL(DepartmentID,30)  ELSE 30 END

Upvotes: 1

John Woo
John Woo

Reputation: 263703

you were close,

AND OrderID = (CASE WHEN @X = 0 THEN 10 ELSE 20 END)
AND DepartmentID = (CASE WHEN @X = 0 THEN DepartmentID ELSE 30 END)

Upvotes: 4

Related Questions