how to cascade CASE sql inside the condition WHERE

Question

I am searching for a word match for the variable- @TextSearchWord
I have a @SearchCriteria variable which may have 5 values!
According to that value, i choose from which field i have to search my word!

-so i need to cascade the "CASE" statement inside the "WHERE" statement only and like other samples here inside the select statement !

SELECT COUNT(WordID) AS WordQty
FROM itinfo_QuranArabicWordsAll
WHERE (SiteID = @SiteID) 
AND (QuranID = @QuranID) 
AND (SuraID BETWEEN @StrtSuraID AND @EndSuraID) 
AND (VerseOrder BETWEEN @StrtVerseSortOrder AND @EndVerseSortOrder)
AND ( 
        -- here is my problem : 
        CASE 
          WHEN (@SearchCriteria = 'DictNM') THEN  (WordDictNM = @TextSearchWord )
          ELSE CASE 
                 WHEN (@SearchCriteria = 'DictNMAlif') THEN (WordDictNMAlif = @TextSearchWord)  
                 ...
               END        
        END
    )

I'm not 100% on tsql, however, the CASE structure is typically: `CASE WHEN THEN WHEN THEN ... END`. That said, if you have multiple values for a single variable, often CASE also allows: `CASE WHEN THEN WHEN THEN .... END` ... might be either of those work for you ? — Ditto, Apr 21 '15 at 15:25
[This](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) may help a bit. `CASE` expressions can be nested. — HABO, Apr 21 '15 at 15:38

score 5 · Accepted Answer · answered Apr 21 '15 at 15:27

You don't need a CASE statement for this.

SELECT     COUNT(WordID) AS WordQty
FROM         itinfo_QuranArabicWordsAll
WHERE (SiteID = @SiteID) 
    AND (QuranID = @QuranID) 
    AND (SuraID BETWEEN @StrtSuraID AND @EndSuraID) 
    AND (VerseOrder BETWEEN @StrtVerseSortOrder AND @EndVerseSortOrder)
    AND (
        (@SearchCriteria = 'DictNM' AND WordDictNM = @TextSearchWord )
        OR (@SearchCriteria = 'DictNMAlif' AND WordDictNMAlif = @TextSearchWord)
        ...
)

ok, thank you i will take this method, i think case will not work in my issue!! — ghalib ghniem, Apr 21 '15 at 17:17

score 3 · Answer 2 · answered Apr 21 '15 at 15:27

3

SQL WHERE clauses: Avoid CASE, use Boolean logic.

....
AND(
    ( @SearchCriteria = 'DictNM'     AND WordDictNM     = @TextSearchWord )   
 OR ( @SearchCriteria = 'DictNMAlif' AND WordDictNMAlif = @TextSearchWord ) 
)

answered Apr 21 '15 at 15:27

Tim Schmelter

450,073
74
686
939

how to cascade CASE sql inside the condition WHERE

2 Answers2