8

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?

Ry-
  • 218,210
  • 55
  • 464
  • 476
Smudger
  • 10,451
  • 29
  • 104
  • 179
  • Why not select it into a table variable or temporary table? – makciook Jul 19 '13 at 12:05
  • A **single variable** can only ever hold **a single value**. If you need multiple values, you need to use a **table variable** in SQL Server – marc_s Jul 19 '13 at 12:19

7 Answers7

14

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)
Christian Specht
  • 35,843
  • 15
  • 128
  • 182
  • 3
    Seems not much different from "select * from cars where make in ('BMW', 'Toyota', 'Nissan')". But they were put into table variable. This is wasteful, unless using the variable more than once. – M H Oct 10 '18 at 15:04
  • 1
    Of course it’s not that much different...but that’s exactly what the OP asked for: “How can I store these parameters in a variable?” – Christian Specht Oct 10 '18 at 20:56
6

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 );
Kenneth Fisher
  • 3,692
  • 19
  • 21
2

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
EzLo
  • 13,780
  • 10
  • 33
  • 38
user10916093
  • 21
  • 1
1

why not?

SELECT * FROM cars WHERE make IN (SELECT DISTINCT(make) FROM carsforsale)
Novice
  • 558
  • 2
  • 9
  • 21
Diego
  • 34,802
  • 21
  • 91
  • 134
1
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
Mohit Kotak
  • 61
  • 1
  • 10
0

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 (....)
Joe Taras
  • 15,166
  • 7
  • 42
  • 55
0

In TSQL you can use STRING_AGG this way

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