case in a where clause

I know this code in the WHERE clause is wrong but I want to use the first logic otherwise use the second logic.  How would I do this?

Case  
		  when @program = 'Y' then
		  
                    OR prog.description NOT IN (
                                         SELECT [description]
                                         FROM Description
                                         WHERE field1 = 'colorseg1'
                                     ) 

	       else 
		   	
		  OR prog.description IN  (
                                         SELECT [description]
                                         FROM NHPData.dbo.xref_programs
                                         WHERE ai_medcom = 'Medicare'
                                     ) 
           end 

Open in new window

vbnetcoderAsked:
Who is Participating?
 
Pawan KumarDatabase ExpertCommented:
can you pls provide full code and what you are trying to achieve?

IF @program = 'Y'
BEGIN
	SELECT * FROM
	tableName
	WHERE prog.description NOT IN (
                                         SELECT [description]
                                         FROM Description
                                         WHERE field1 = 'colorseg1'
                                     ) 
END	
ELSE
	SELECT * FROM
	tableName
	WHERE prog.description IN  (
                                         SELECT [description]
                                         FROM NHPData.dbo.xref_programs
                                         WHERE ai_medcom = 'Medicare'
                                     )

Open in new window

1
 
vbnetcoderAuthor Commented:
do I have to rewrite the whole long query again? is there any way I can just select have the case statement determine what where clause to use?
0
 
Pawan KumarDatabase ExpertCommented:
or we can do dynamic sql ..

DECLARE @SQL AS VARCHAR(MAX) = ''
DECLARE @program AS VARCHAR(1) = ''

SET @SQL = ' Your Query'

IF @program = 'Y'

	SET @SQL = @SQL + 'OR prog.description NOT IN (
                                         SELECT [description]
                                         FROM Description
                                         WHERE field1 = ''colorseg1''
                                     )'

ELSE
	SET @SQL = @SQL + ' prog.description IN  (
                                         SELECT [description]
                                         FROM NHPData.dbo.xref_programs
                                         WHERE ai_medcom = ''Medicare''
                                     ) ' 
EXEC ( @SQL )

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Typically you wouldn't need a CASE statement in the WHERE clause since it can be easily done only with Boolean expressions:
WHERE (
	(@program = 'Y' AND 
		prog.description NOT IN (SELECT [description]
                                 FROM Description
                                 WHERE field1 = 'colorseg1')
	) OR 
		prog.description IN (SELECT [description]
                             FROM NHPData.dbo.xref_programs
                             WHERE ai_medcom = 'Medicare') 
	)

Open in new window

1
 
awking00Commented:
It's difficult to determine what it is precisely that you're trying do accomplish. However, case statements are executed in order of the first met condition, then the second, etc. So, for example, the following case statement:
case when condition1 = something or condition2 = something then do A
         when condition1 = something and condition3 = something then do B
         when condition4 = something then do C
         else do D
end
Would execute A when either condition1 or condition2 are met (i.e. true),
execute B when both condition1 and condition3 are met,
execute C when condition4 is met,
and execute D when none of the above conditions are met.
Maybe a brief description of the various conditions and their priority might be enough to get you on the right track without having provide any very lengthy queries.
0
 
Scott PletcherSenior DBACommented:
Vitor's got roughly the gist of it.  More specifically, like this:

WHERE (...other_conditions...)
        OR ( (@program = 'Y' AND prog.description NOT IN ( ... )) OR
                 (@program <> 'Y' AND prog.description IN ( ... )) )
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
vbnetcoder, do you still need help with this question?
0
 
Pawan KumarDatabase ExpertCommented:
Hi Vbnetcoder,
A feedback will be appreciated :)
Thanks !
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.