How to use a variable with multiple values as a parameter in the where clause sql

I am trying to create a “where clause” that can handle multi-valued parameter but not getting it to work.

CREATE TABLE [dbo].[Product]
(
    [ProductKey] [int] IDENTITY(1,1) NOT NULL,
    [Productnr] [nvarchar](25) NULL,
    [Color] [nvarchar](15) NOT NULL,
) 

INSERT INTO [dbo].[Product] ([Productnr], [Color])  
VALUES ('45678', 'Red'),
       ('98765', 'Blue'),
       ('46547', 'Black')

With the above sample table I wish to create a query that filters the rows with colors red and blue using a variable parameter like the following:

DECLARE @Color varchar(50) = 'Red,Blue';

SELECT *
FROM [dbo].[Product] 
WHERE [Color] IN (@Color)

I tried using the STRING_SPLIT function but didn’t get it to work (result output is empty)

DECLARE @Color varchar(50) = 'Red,Blue';

SELECT *
FROM [dbo].[Product] 
WHERE EXISTS (SELECT * FROM STRING_SPLIT(@Color, ',') 
              WHERE [Color] IN (SELECT @Color 
                                FROM STRING_SPLIT(@Color, ',')))

If remove the Blue from the Variable and only pass “Red” it works create but not when I try to pass multiple values.

Answer

You can use IN:

SELECT p.*
FROM [dbo].[Product] p 
WHERE p.Color IN (SELECT s.value FROM STRING_SPLIT(@Color, ',') s)