CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2casewhere-clause
Ege Bayrak
Ege Bayrak

Reputation: 1199

Usage of case clause in where clause

I need to add a filter to a stored procedure depending on a dropdowns value

Available values are 0,1,2,3,4,5. @TescilDurum is my parameter.

0 = ALL
1 = TescilDurumId IN 4 OR 5
2 = t.ID IS NULL
3 = dncts.RegistrationCode IS NULL
4 = hsm.ID IS NULL AND dncts.RegistrationCode IS NULL IS NOT NULL
5 = hsm.ENSMRN IS NULL

So far I tried this from a similar question answer on internet but I'm not sure if it is right thing to do. I'm not sure if this "reverse logic" is correct way to do this. And the order by after it gives an error too...

AND 1 =
    CASE WHEN @TescilDurum = 1 AND t.TescilDurumId NOT IN (4,5) THEN 0 
    ELSE CASE WHEN @TescilDurum = 2 AND t.Id IS NOT NULL THEN 0
    ELSE CASE WHEN @TescilDurum = 3 AND hsm.EnsMRN IS NOT NULL THEN 0
    ELSE CASE WHEN @TescilDurum = 4 AND hsm.Id IS NOT NULL AND dncts.RegistrationCode IS NULL THEN 0
    ELSE CASE WHEN @TescilDurum = 5 AND hsm.EnsMRN IS NOT NULL THEN 0
    ELSE 1 END

Upvotes: 1

Views: 103

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175974

You can simplify it:

WHERE 
  (CASE 
       WHEN @TescilDurum = 0 THEN 1
       WHEN @TescilDurum = 1 AND TescilDurumId IN (4,5) THEN 1
       WHEN @TescilDurum = 2 AND t.ID IS NULL THEN 1
       WHEN @TescilDurum = 3 AND dncts.RegistrationCode IS NULL THEN 1
       WHEN @TescilDurum = 4 AND hsm.ID IS NULL AND dncts.RegistrationCode IS NOT NULL THEN 1
       WHEN @TescilDurum = 5 AND hsm.ENSMRN IS NULL THEN 1
       ELSE NULL    -- this is default and can be skipped if needed
  END) = 1

EDIT:

For anyone who is afraid that query above is always 1 = 1:

CASE has default ELSE NULL clause so when there is no match before entire statement will return NULL and NULL = 1 => NULL.

DECLARE @v INT = 4;

SELECT 1
WHERE(CASE WHEN @v = 1 THEN 1
           WHEN @v = 2 THEN 1
           WHEN @v = 3 THEN 1
           ELSE NULL    -- this is default and can be skipped if needed
      END) = 1
 -- empty resultset

and:

CREATE TABLE #tab(ID INT, col VARCHAR(100));

INSERT INTO #tab
VALUES (1, 'aa'), (2, 'bbb'), (3, NULL), (4, 'aa');

-- DROP DOWN MENU
-- 1 all records
-- 2 only those which ID is in (2,3)
-- 3 only those which has col = 'aa'
-- 4 only those which has NULL value in col

DECLARE @variable INT = 3;

SELECT *
FROM #tab
WHERE 
(
  CASE WHEN @variable = 1 THEN 1
       WHEN @variable = 2 AND ID IN (2,3) THEN 1
       WHEN @variable = 3 AND col = 'aa'  THEN 1
       WHEN @variable = 4 AND col IS NULL THEN 1
       ELSE NULL    -- this is default and can be skipped if needed
 END
) = 1

Keep in mind that that there is compound condition with AND so correct rows will be returned.

LiveDemo

Upvotes: 6

Related Questions