Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite
1

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?

share|improve this question
Why not select it into a table variable or temporary table? – makciook yesterday
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 yesterday

4 Answers

up vote 1 down vote

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)
share|improve this answer
up vote 1 down vote

why not?

SELECT * FROM cars WHERE make IN (SELECT DISTINCT(make) FROM carsforsale)
share|improve this answer
up vote 0 down vote

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 (....)
share|improve this answer
up vote 0 down vote

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 );
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.