patternsqlMinor
Is there a compact way to use a NOT IN (1,2,3) syntax when setting a variable?
Viewed 0 times
variableusewaysyntaxsettingwhentherenotcompact
Problem
Expressions like NOT IN (1,2,3) are valid in a where clause:
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:
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
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.
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=0I 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
works fine. Or you could also do
Or (if you are on 2012+)
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=0works 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
ENDOr (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=0SET @ignoretype = CASE
WHEN @record_type NOT IN ( 2, 3, 7, 18, 19, 20, 21, 12, 13, 22 )
THEN 1
ELSE 0
ENDSET @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.