Operators similar to 'in' that compares two sets
You can just JOIN
to real or dynamically derived tables:
DECLARE @var1 INT=2;
DECLARE @var2 INT=15;
DECLARE @var3 INT=-7;
IF EXISTS (SELECT 1 FROM
(VALUES (1),(2),(3))tableNums(number)
INNER JOIN (VALUES (@var1), (@var2), (@var3))tableVars(var)
ON tableNums.number=tableVars.var
)
SELECT 1 --or do something else interesting
ELSE SELECT 0 --or do the opposite
If the list was very long, I'd probably create it in a #temp table in advance instead of doing it in the statement, but the JOIN
and EXISTS
logic would be the same.
Similarly, we could used INTERSECT
instead of JOIN
:
CASE WHEN EXISTS ( SELECT * FROM (VALUES (1), (2), (3)) AS x(i)
INTERSECT
SELECT * FROM (VALUES (@var1), (@var2), (@var3)) AS y(i)
)
THEN 1 ELSE 0 END
Test at dbfiddle.uk.
You can try the following instead of CASE
declare @var1 int, @var2 int, @var3 int
set @var1 = 1
set @var2 = 2
Set @var3 = 3
IF 1 IN (@var1, @var2, @var3) or 2 in (@var1, @var2, @var3) or 3 in (@var1, @var2, @var3)
BEGIN
Select 1;
END
else
BEGIN
select 0;
END