Solved

sql where statement

Posted on 2014-01-31
4
465 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:Kent Olsen
Kent Olsen 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
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…

828 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