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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Acronis Data Cloud 7.8 Enhances Cyber Protection

A closer look at five essential enhancements that benefit end-users and help MSPs take their cloud data protection business further.

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
awking00Information Technology SpecialistCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.