Geek asks and answers

How to use "IN" in CASE on SQL server?

I want to do the following from case when

If @chk='Y'  
    Select * From Table1 Where Column1=@Value  
Else If (@chk='N')
    Select * From Table1 Where Column1 In (Select column2 from Table2)

I think it could be something like:

 Select * 
 From Table1 
 Where 
    Case  When @chk='Y' Then
        Column1=@Value
    Else
        Column1 In (Select column2 from Table2)
 End

I know that there are alternative solutions besides case when. But can this be done with help case when?

+3
source share
4 answers

CASE is an expression that returns a single value. It is not used for flow control.

WHERE (Column1 = @Value AND @chk = 'Y')
OR (@chk <> 'Y' AND Column1 IN (SELECT column2 FROM table2));
+6
source

CASEused for internal evaluation of expressions. This does not apply to dynamic offers WHERE.

The solution you ask is to use a sentence WHEREgrouped in parentheses:

 Select * From Table1 
 Where 
 (@chk='Y' AND Column1=@Value)
 OR
 (@chk <> 'Y' AND Column1 In (Select column2 from Table2))
+4
source

CASE WHEN, . ( )

Select * From Table1 
Where 
   CASE @Chk
   WHEN 'Y' THEN
        CASE WHEN Column1=@Value THEN 1 END 
   WHEN 'N' THEN
        CASE WHEN Column1 In (Select column2 from Table2) THEN 1 END
   END = 1

CASE .

, OR: http://www.sqlfiddle.com/#!6/29531/2

:

-- using CASE WHEN to convince your RDBMS to short-circuit things:
select count(*)
from usermessages um
join "user" u    
on
(case when um.friendId = 1 then um.sourceUserId else um.friendId end) = u.userId;


-- pure boolean approach, RDBMS can't short-circuit the OR expression
select count(*)
from usermessages um
join "user" u    
on 
(um.friendId = 1 and um.sourceUserId = u.userId)
or 
(um.friendId = u.userId);

http://www.sqlfiddle.com/#!6/29531/2, 88 , 4,7 . .

, , . CASE WHEN - ( ). - ,

+1

Case WHERE. :

Select *
From Table1
Where (Case When @chk='Y' and Column1=@Value the 'Y'
            Else Column1 In (Select column2 from Table2)
       End) = 'Y'

, . , , , , , ​​ . .

, , , . WHERE . , CASE.

- . SQL, , SQL WHERE:

where isnumeric(val) = 1 and cast(val to float) < 100.0

, :

where (case when isnumeric(val) = 1 then cast(val to float) end) < 100.0

, , , WHERE .

0
source

More articles:


All Articles