SQL Server store 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 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?

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
– marc_s, Commented Jul 19, 2013 at 12:19

Christian Specht · Accepted Answer · 2013-07-19 12:07:20Z

16

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)

answered Jul 19, 2013 at 12:07

Christian Specht

36.6k16 gold badges132 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M H Over a year ago

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.

Christian Specht Over a year ago

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?”

Kenneth Fisher · Accepted Answer · 2013-07-19 12:01:59Z

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

EzLo · Accepted Answer · 2019-01-15 09:26:38Z

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

edited Jan 15, 2019 at 9:26

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

answered Jan 15, 2019 at 9:23

user10916093

211 bronze badge

Comments

Novice · Accepted Answer · 2013-07-19 12:32:42Z

1

why not?

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

edited Jul 19, 2013 at 12:32

Novice

5582 gold badges9 silver badges21 bronze badges

answered Jul 19, 2013 at 12:08

Diego

36.5k21 gold badges94 silver badges138 bronze badges

1 Comment

Smudger Over a year ago

Sorry, just an example, not my actual requirement. Thanks.

Mohit Kotak · Accepted Answer · 2014-09-09 07:55:29Z

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

answered Sep 9, 2014 at 7:55

Mohit Kotak

611 silver badge10 bronze badges

Comments

Joe Taras · Accepted Answer · 2013-07-19 11:59:59Z

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 (....)

answered Jul 19, 2013 at 11:59

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Comments

karma · Accepted Answer · 2021-04-28 13:10:04Z

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

edited Apr 28, 2021 at 13:10

answered Apr 28, 2021 at 12:54

karma

114 bronze badges

Collectives™ on Stack Overflow

SQL Server store multiple values in sql variable

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

Comments

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related