I'm trying to use a @List Table to feed a Where IN statement. I keep getting the error, "Must Declare the scalar variable "@List".
I know I can simply use the select statement in the WHERE IN part, but I'm needing to find out conceptually if this is possible for a larger SQL statement that I can't share the whole thing.
DECLARE @EarliestDate date Set @EarliestDate = '2020-01-01'
DECLARE @LatestDate date Set @LatestDate = '2020-01-05'
DECLARE @List TABLE(DATAPOINT varchar(20))
INSERT INTO @List SELECT number1 + CONVERT(varchar(5),number2)
FROM
tbl1
INNER JOIN msts tb2 on tbl1.j = tbl2.j and tbl1.s = tbl2.s
INNER JOIN tbl3 on tbl3.r = tbl1.j and tbl3.re = tbl1.s
AND tbl2.date > @EarliestDate
AND tbl2.date < @LatestDate
SELECT number3
, number4
FROM table1 ci
WHERE 1=1
AND ci.number1 + CONVERT(varchar(5),ci.number2) IN (**@List**)
To use in() properly you must specify a query, not just put the table variable you declared and inserted data into.
Example:
DECLARE @EarliestDate date Set @EarliestDate = '2020-01-01'
DECLARE @LatestDate date Set @LatestDate = '2020-01-05'
DECLARE @List TABLE(DATAPOINT varchar(20))
INSERT INTO @List SELECT number1 + CONVERT(varchar(5),number2)
FROM
tbl1
INNER JOIN msts tb2 on tbl1.j = tbl2.j and tbl1.s = tbl2.s
INNER JOIN tbl3 on tbl3.r = tbl1.j and tbl3.re = tbl1.s
AND tbl2.date > @EarliestDate
AND tbl2.date < @LatestDate
SELECT number3
, number4
FROM table1 ci
WHERE 1=1
AND ci.number1 + CONVERT(varchar(5),ci.number2) IN (select DATAPOINT from @List)