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.
Advertisement
Answer
You can use IN
:
SELECT p.* FROM [dbo].[Product] p WHERE p.Color IN (SELECT s.value FROM STRING_SPLIT(@Color, ',') s)