Assign multiple values in declaration part

Question

I need to achieve the results by providing multiple declaration values. How can it be done?

DECLARE @ID INT

SET @ID = 1   --instead I need to provide multiple values like @ID = 1,2,3

SELECT C.Name, C.EmployeeID 
FROM Cases c
WHERE C.EmployeeID = @ID

Table

Name   |    ID
-------+---------
A           1
B           2
C           3
D           4
E           5
F           6

And the results should be:

A           1
B           2
C           3

You can't. A scalar variable holds a single value. You need a table variable (or temp table or actual table) to store multiple values, using a join or IN clause for your query. — SMor, Dec 22 '20 at 17:13
You should explain where the values come from & if there are always the same number of them. — Alex K., Dec 22 '20 at 17:14
FYI, SQL Server 2008 reached end of life well over a year ago; you should really be looking at upgrade paths ASAP. — Thom A, Dec 22 '20 at 17:14

score 2 · Accepted Answer · answered Dec 22 '20 at 17:14

2

One method is with a table variable:

DECLARE @IDs TABLE(ID INT);
INSERT INTO @IDs VALUES(1),(2),(3);

SELECT C.Name, C.EmployeeID 
FROM dbo.Cases AS c
WHERE C.EmployeeID IN(SELECT ID FROM @IDs);

answered Dec 22 '20 at 17:14

Dan Guzman

43,250
3
46
71

score 0 · Answer 2 · answered Dec 22 '20 at 18:02

A very simple way to do this is with the XML datatype.

DECLARE @ids xml = '<ids><id>1</id><id>2</id><id>3</id></ids>';

SELECT 
    C.[Name],
    C.EmployeeID 
FROM Cases c 
WHERE C.EmployeeID IN (

    SELECT x.f.value( '.', 'int' ) FROM @ids.nodes ( '//ids/id' ) x( f )

);

Assign multiple values in declaration part

2 Answers2