Solved

sql server using case statement in where clause

Posted on 2016-08-08
3
125 Views
Last Modified: 2016-08-08
currently I have a where clause that looks like this:
where rs.status in ('Open', 'Confirmed', 'Arrived')

this is what I'm trying:
where case
     when @hp=1 then in ('Open', 'Confirmed', 'Arrived')
     else in ('Open', 'Confirmed', 'Arrived', 'High Probability')
     end

what am I doing wrong?
0
Comment
Question by:dhenderson12
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 

Author Comment

by:dhenderson12
ID: 41747505
sorry, mis-typed.
this is what I'm trying:
where case
     when @hp=1 then rs.status in ('Open', 'Confirmed', 'Arrived')
     else rs.status in ('Open', 'Confirmed', 'Arrived', 'High Probability')
     end
0
 
LVL 66

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41747518
Give this a whirl..
WHERE
   ( @hp = 1 AND rs.status in ('Open', 'Confirmed', 'Arrived')) OR 
   ( @hp <> 1 AND rs.status in ('Open', 'Confirmed', 'Arrived', 'High Probability'))

Open in new window

0
 

Author Comment

by:dhenderson12
ID: 41747592
sorry, that didn't work ... got the same records.  ended up doing an "IF / ELSE" statement .
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question