CODEWITHSUNDEEP
sqlsql-serverarraysvariablesstored-procedures
Smudger
Smudger

Reputation: 10781

SQL Server store multiple values in sql variable

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?

Upvotes: 9

Views: 97622

Answers (7)

karma
karma

Reputation: 11

In TSQL you can use STRING_AGG this way

select STRING_AGG(car_name,',') as csv
from cars 
where make in ('BMW', 'Toyota', 'Nissan')

Upvotes: 0

user10916093
user10916093

Reputation: 21

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 DATA1

select @car_name

Upvotes: 2

Mohit Kotak
Mohit Kotak

Reputation: 61

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

Upvotes: 1

Diego
Diego

Reputation: 36146

why not?

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

Upvotes: 1

Christian Specht
Christian Specht

Reputation: 36431

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)

Upvotes: 16

Kenneth Fisher
Kenneth Fisher

Reputation: 3812

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 Cars
WHERE ','+@CarOptions+',' LIKE ',%'+CAST(Make AS varchar)+',%'

A 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

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 );

Upvotes: 6

Joe Taras
Joe Taras

Reputation: 15379

you can use JOIN statement.

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

If you want filter your list of carsforsale you can add

WHERE s.id in (....)

Upvotes: 0

Related Questions