CODEWITHSUNDEEP
sql-servert-sqlvariablesdeclaretable-variable
PeterH
PeterH

Reputation: 1040

Declare and set a variable with more than one possible value

Is it possible to set a variable to have more than one value?

In the example below, I would like to set the @variable and use store numbers 1,4,7 and 12

And then use the variable in the WHERE statment.

The below is purely an example to see if this is possible or not

Declare @Variable INT;

Set @Variable = IN(1,4,7,12)

Select *
From dbo.EUactivestores eu
Where eu.[Store No] = @Variable

Any advice on if this is possible, or something similar would be great.

I believe it could help with making a more dynamic query

Upvotes: 0

Views: 2911

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67341

Use a table variable like here

DECLARE @tbl TABLE(v INT);
INSERT INTO @tbl VALUES(1),(4),(7),(12);

You can use this in WHERE clause like here:

WHERE eu.[Store No] IN(SELECT v FROM @tbl);

Fully working example:

DECLARE @tbl TABLE(v INT);
INSERT INTO @tbl VALUES(1),(4),(7),(12);

DECLARE @mockup TABLE(ID INT IDENTITY,SomeValue VARCHAR(100));
INSERT INTO @mockup VALUES('val 1'),('val 2'),('val 3'),('val 4'),('val 5')
                         ,('val 6'),('val 7'),('val 8'),('val 9'),('val 10')
                         ,('val 11'),('val 12'),('val 13'),('val 14'),('val 15');

--only values from your table

SELECT * FROM @mockup AS m
WHERE m.ID IN(SELECT v FROM @tbl);

--or the negation of the above

SELECT * FROM @mockup AS m
WHERE m.ID NOT IN(SELECT v FROM @tbl);

Upvotes: 7

Related Questions