HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
variableusewaysyntaxsettingwhentherenotcompact

Problem

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  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.

Solution

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)

Code Snippets

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

Context

StackExchange Database Administrators Q#30583, answer score: 7

Revisions (0)

No revisions yet.