Solved

sql where statement

Posted on 2014-01-31
4
446 Views
Last Modified: 2014-01-31
HI, NEED THIS TO WORK, PLEASE HELP



DECLARE @sumthing

SELECT * FROM table1
WHERE
    CASE WHEN @sumtin = 1
       THEN (table1.field1 IN ('A', 'B', 'AH') )
       ELSE (table1.field1 NOT IN ('A', 'B', 'AH') ) END
0
Comment
Question by:Ess Kay
4 Comments
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 400 total points
ID: 39825310
you need to write it as below

DECLARE @sumthing

SELECT * FROM table1
WHERE (@sumtin = 1 and (table1.field1 IN ('A', 'B', 'AH') ))
or (@sumtin <> 1 and (table1.field1 NOT IN ('A', 'B', 'AH'))

Open in new window

0
 
LVL 45

Assisted Solution

by:Kdo
Kdo earned 100 total points
ID: 39825314
Hi esskay,

CASE returns a single value so the structure that you've defined isn't legal SQL.

But you can do it without the CASE construct.

SELECT * FROM table1
WHERE
    (@sumtin = 1 AND table1.field1 IN ('A', 'B', 'AH') )
OR (@sumtin <> 1 AND table1.field1 NOT IN ('A', 'B', 'AH') )


That should do the trick for you.

Good Luck,
Kent
0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 39825317
The above answer is correct.  CASE blocks work great, but in a WHERE clause it's more (effective, easier to read, ??) just to use logical expressions like AND or OR, and parentheses to set the execution.
0
 
LVL 15

Author Closing Comment

by:Ess Kay
ID: 39825329
Thanks, not enough coffee today
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now