• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • Last Modified:

sql where statement

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
Ess Kay
Asked:
Ess Kay
2 Solutions
 
Surendra NathTechnology LeadCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
Ess KayEntrapenuerAuthor Commented:
Thanks, not enough coffee today
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now