Take the 2-minute tour ×
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. It's 100% free, no registration required.

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

SELECT foo FROM bar WHERE 
  ((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
else
     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

1 Answer 1

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
ELSE
  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
                  END 

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
1  
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).... msdn.microsoft.com/en-us/library/hh213574.aspx –  Warren P Dec 17 '12 at 14:59

Your Answer

 
discard

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.