Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

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