SQL Server store multiple values in sql variable

arrays, sql, sql-server, stored-procedures, variables

Solution

You can use a table variable:

declare @caroptions table
(
    car varchar(1000)
)

insert into @caroptions values ('BMW')
insert into @caroptions values ('Toyota')
insert into @caroptions values ('Nissan')

select * from cars where make in (select car from @caroptions)

Problem

I have the following query: ``` select * from cars where make in ('BMW', 'Toyota', 'Nissan') ``` What I want to do is store the where parameters in a SQL variable. Something like: ``` declare @caroptions varchar(max); select @caroptions = select distinct(make) from carsforsale; print @caroptions; select * from cars where make in (@caroptions) ``` Problem is the print of `@caroptions` only has the last result returned from: ``` select distinct(make) from carsforsale; ``` I want it to store multiple values. Any ideas?

Original source