SQL Server store multiple values in sql variable

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)

I wrote about this here if you want to see it in detail. In the mean time, you can't do it exactly how you are thinking.

Your choices are:

Using the LIKE command:

DECLARE @CarOptions varchar(100)SET @CarOptions = 'Ford, Nisan, Toyota'SELECT *FROM CarsWHERE ','+@CarOptions+',' LIKE ',%'+CAST(Make AS varchar)+',%'

A spliter function

DECLARE @CarOptions varchar(100)SET @CarOptions = 'Ford, Nisan, Toyota'SELECT Cars.*FROM CarsJOIN DelimitedSplit8K (@CarOptions,',') SplitString    ON Cars.Make = SplitString.Item

Dyanmic SQL

DECLARE @CarOptions varchar(100)SET @CarOptions = 'Ford, Nisan, Toyota'DECLARE @sql nvarchar(1000)SET @sql = 'SELECT * ' +             'FROM Cars ' +             'WHERE Make IN ('+@CarOptions+') 'EXEC sp_executesql @sql

In the mean time your best option is going to be to get rid of the variable completely.

SELECT * FROM cars WHERE make IN (SELECT make FROM carsforsale );

Use CTE for storing multiple values into a single variable.

;WITH DATA1 AS (    select car_name    from cars     where make in ('BMW', 'Toyota', 'Nissan'))SELECT @car_name = CONCAT(@car_name,',',car_name)FROM DATA1select @car_name