Solved

sql server using case statement in where clause

Posted on 2016-08-08
3
95 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
  • 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 65

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql Server group by 10 51
PROPERCASE SCRIPT IN SQL 3 23
Why I am having this error in my query? 2 32
Can I skip a node in XML? 9 34
     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

749 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