Array var in SQL Server 2008

Question

I'm trying to figure out how to create a var in Sql Server which contains an array of values, searched here in Stack but nothing consistent... this is how I imagined it could be like:

declare @partners as nvarchar(max) = ('partner1','partner2');
select * from partners
where partner in (@partners);

Is it possible in SQL Server? How?

Thanks!

Answer 1

You can do it with table

declare @partnersList as table(partnerName varchar(100))
insert into @partnersList values('partner1'),('partner2')
SELECT * 
FROM partners
WHERE 'partner' in (SELECT partnerName FROM @partnersList)

• jvbs: Disappointed that SQL Server do not support this kind of logic, but this solution is useful and solved my problem, thank you!

Answer 2

You can't do so, but you can use a table variable to get the same behavior:

declare @partners TABLE (
 Name nvarchar(max)
)
INSERT @partners
VALUES
 ('partner1'),
 ('partner2')
select * from partners
where partner in (
 SELECT Name 
 FROM @partners
)

Answer 3

Or you can execute it as dynamic:

declare @partners as nvarchar(max) = ' ''partner1'',''partner2'' '
declare @sql nvarchar(Max)
Set @sql='SELECT * FROM partners WHERE [partner] IN ('+ @partners +')'
exec sp_executesql @sql

• Alejandro: This exposes the code to SQL injection and should be, really, the very last resort.