Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Expressions like NOT IN (1,2,3) are valid in a where clause:

  ((record_type NOT IN (2, 3, 7, 18, 19, 20, 21,12,13,22))

However if I want to move an elaborate expression composed of checks like the above from the WHERE clause to a SET @Var= statement, is there a way to do that without rewriting it like this:

declare @record_type int
set  @record_type  = 1

declare @ignoretype bit
IF    (@record_type <> 2)and(@record_type <> 3)and(@record_type <> 7)and
      (@record_type <> 12)and(@record_type <> 13)and
      ( (@record_type < 18) or (@record_type > 22))
     set @ignoretype=1
     set @ignoretype=0

I feel like it's really gross that what used to fit in one readable line is now a gigantic gross mess. Am I missing some syntax in SQL that is "NOT IN (1,2,3)" equivalent that works in set @var = or IF expressions? I would have written the above as:

select @ignoretype = (@record_type <> 2) ....

But you can't even use "<>" inequalities in a select statement in Microsoft SQL Server, which might be normal situation for DBAs and SQL experts, but as a relatively new SQL Server guy, I'm still finding the various syntax limits in various contexts confusing.

share|improve this question

There is no boolean datatype in SQL Server which is why select @ignoretype = (@record_type <> 2) doesn't work.

IF @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
  SET @ignoretype=1
  SET @ignoretype=0

works fine. Or you could also do

SET @ignoretype = CASE
                    WHEN @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 ) 
                    THEN 1
                    ELSE 0

Or (if you are on 2012+)

SET @ignoretype = IIF(@record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 ) ,1,0)
share|improve this answer
Wow. thanks for the IIF (2012+) idea. When we move to SQL 2012 that would be great. Otherwise CASE is at least more readable than my if (a<>1)and(a<>2).... – Warren P Dec 17 '12 at 14:59

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.