sql-serversql-server-2017ssms-17

Assign multiple values in declaration part

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
Solution

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