Trying to use a variable in a SQL WHERE IN statement

Question

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**) 

Answer 1

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) 

Answer 2

Just to be complete you can also solve this with a CTE -- which is sometimes faster. I also used a join here since that too can sometime let the compiler optimize.

DECLARE @EarliestDate date Set @EarliestDate = '2020-01-01';

DECLARE @LatestDate date Set @LatestDate = '2020-01-05';

WITH list AS 
(
  SELECT number1  + CONVERT(varchar(5),number2) as DATAPOINT
  FROM tbl1 
  JOIN msts tb2 on tbl1.j = tbl2.j and tbl1.s = tbl2.s
  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
JOIN list on ci.number1 + CONVERT(varchar(5),ci.number2) = DATAPOINT ;