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: decla

Solution 1:

You can use a table variable:

declare@caroptionstable
(
    car varchar(1000)
)

insertinto@caroptionsvalues ('BMW')
insertinto@caroptionsvalues ('Toyota')
insertinto@caroptionsvalues ('Nissan')

select*from cars where make in (select car from@caroptions)

Solution 2:

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@CarOptionsvarchar(100)
SET@CarOptions='Ford, Nisan, Toyota'SELECT*FROM Cars
WHERE','+@CarOptions+','LIKE',%'+CAST(Make ASvarchar)+',%'

A spliter function

DECLARE@CarOptionsvarchar(100)
SET@CarOptions='Ford, Nisan, Toyota'SELECT Cars.*FROM Cars
JOIN DelimitedSplit8K (@CarOptions,',') SplitString
    ON Cars.Make = SplitString.Item

Dyanmic SQL

DECLARE@CarOptionsvarchar(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 );

Solution 3:

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

Solution 4:

why not?

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

Solution 5:

Fetch1valueintableand store in variable    
=======================================================================================Declare@queryintselect@query= p.ProductID From Product p innerjoin ReOrdering as r on 
    p.ProductID = r.ProductID and r.MinQty >= p.Qty_Available

    print @query