Passing multiple values into variable

Question

I'm trying to pass a value from a table into a variable which I can then use in a query. The problem is that I need the variable to have multiple values, which I've never done before. Below is an example of what I'm trying to do.

CompanyID has multiple values for control number. How would I be able to pass multiple values into the variable? The query I'm using is using Open Query so I can't join to a temp table.

 declare @t table( companyid int, control_number int)
 insert into @t
 values(5555, 777),
 (5555, 720),
 (5555, 234),
 (111, 345),
 (111, 356)

 select *
 from @t

 declare @CN int
 set @CN = (select control_number from @t where companyid = 5555)

 select *
 from @table
 where control_number IN(@CN)

[Parameterize an SQL IN clause](https://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause) - [STRING_SPLIT](https://stackoverflow.com/a/36980115/5070879) — Lukasz Szozda, Mar 13 '20 at 18:16
Why do you need to "store" the resulset of the first query AT ALL? Smells like a [XY problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). — SMor, Mar 13 '20 at 18:21
And Erland discusses this misconception [here](http://www.sommarskog.se/arrays-in-sql.html) — SMor, Mar 13 '20 at 18:22
I'm not storing it. I'm just providing data for people to use here. — jackstraw22, Mar 13 '20 at 18:22
Please check the answer [here](https://stackoverflow.com/a/60675669/4690672) — Nayanish Damania, Mar 13 '20 at 18:40
So I just realized that my SP is using an Open Query and I can't join to any temp tables. — jackstraw22, Mar 13 '20 at 19:22

score 0 · Answer 1 · answered Mar 13 '20 at 18:29

Instead of setting it into variable you can directly use it IN like this :

declare @t table( companyid int, control_number int)
 insert into @t
 values(5555, 777),
 (5555, 720),
 (5555, 234),
 (111, 345),
 (111, 356)

 select *
 from @t

 select *
 from @table
 where control_number IN(select control_number from @t where companyid = 5555)

Try this out I haven't able to run this because you haven.t shared schema of @table

score 0 · Answer 2 · answered Mar 13 '20 at 18:32

Another way to skin the cat. Make your second variable a table variable. Then just join to it.

declare @CN table (cntNbr int)
insert @CN (cntNbr)
select control_number from @t where companyid = 5555;

select t.*
from @t as t
join @CN as c
  on c.cntNbr = t.control_number;

Rextester link: https://rextester.com/SCYCX42852

Nayanish Damania · Answer 3 · 2020-03-13T19:03:05.870

Method 1 (Dynamic SQL):

CREATE TABLE #temp ( companyid INT, control_number INT )
INSERT INTO #temp
VALUES (5555, 777),
(5555, 720),
(5555, 234),
(111, 345),
(111, 356)

SELECT * FROM #temp

DECLARE @controlNum VARCHAR(MAX)
SELECT @controlNum = ISNULL(@controlNum + ', ', '') + CAST(control_number AS VARCHAR) FROM #temp WHERE companyid = 5555

DECLARE @sql NVARCHAR(MAX) = 'SELECT * FROM #temp WHERE control_number IN ( ' + @controlNum + ')'
EXEC sp_executesql @sql

Method 2:

DECLARE @temp TABLE( companyid INT, control_number INT )
INSERT INTO @temp
VALUES (5555, 777),
(5555, 720),
(5555, 234),
(111, 345),
(111, 356)

SELECT * FROM @temp

SELECT * FROM @temp WHERE control_number IN (SELECT control_number FROM @temp WHERE companyid = 5555)

Passing multiple values into variable

3 Answers3