SQL Server stores multiple values ​​in sql variable

Question

I have the following query:

select * from cars where make in ('BMW', 'Toyota', 'Nissan') 

What I want to do is save the parameters that are in the SQL variable.

Sort of:

 declare @caroptions varchar(max); select @caroptions = select distinct(make) from carsforsale; print @caroptions; select * from cars where make in (@caroptions) 

The problem is that @caroptions print @caroptions has the last result returned from:

 select distinct(make) from carsforsale; 

I want to save multiple values.

Any ideas?

Answer 1

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) 

Answer 2

I wrote about it here if you want to see it in detail. Meanwhile, you cannot do it exactly as you think.

Your options:

Using the LIKE command:

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

Spliter function

 DECLARE @CarOptions varchar(100) SET @CarOptions = 'Ford, Nisan, Toyota' SELECT Cars.* FROM Cars JOIN 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 

At that time, your best option is to completely get rid of the variable.

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

Answer 3

why not?

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

Answer 4

 Fetch 1 value in table and store in variable ======================================================================================= Declare @query int select @query = p.ProductID From Product p inner join ReOrdering as r on p.ProductID = r.ProductID and r.MinQty >= p.Qty_Available print @query 

Answer 5

Use CTE to store multiple values ​​in 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 DATA1 select @car_name 

Answer 6

you can use the join operator.

 SELECT distinct c.* FROM cars c JOIN carsfrosale s ON s.id = c.fk_s 

If you want to filter your carsforsale list, you can add

 WHERE s.id in (....)