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

Which line gives the error? Have you tried substituting the contents of the variable and running that. — nicomp, Aug 26 '20 at 20:55
The line that gives the error is the final line, at the WHERE IN statement using @List. I can successfully run the query if I just replace @ list with what I have in the INSERT INTO statement, but that solution won't work in my scenario. I'm having to pass so information from another server and convert the results to a different data type to bring it back in. The other server has no reference of the tables that I am using to make the list, so I was attempting to use a variable. — Jonathan Peters, Aug 26 '20 at 20:59
Maybe writing the last line this way?: WHERE 1=1 AND ci.number1 + CONVERT(varchar(5),ci.number2) IN (Select DATAPOINT From @List) — MundoPeter, Aug 26 '20 at 21:04

score 2 · Accepted Answer · answered Aug 26 '20 at 21:17

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)

Thanks for the input. That seemed to fix it. – Jonathan Peters Aug 26 '20 at 21:28 — Jonathan Peters, Aug 26 '20 at 21:28

score 2 · Answer 2 · answered Aug 26 '20 at 21:41

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 ;

Trying to use a variable in a SQL WHERE IN statement

2 Answers2