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.