Something like this is what you need. The splitter function allows you to do a set-based join to get your query results.

--sample date table

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL

DROP TABLE #TempTable

CREATE TABLE #TempTable (

[ID] INT IDENTITY(1,1) NOT NULL,

[WeekDay] NVARCHAR(50) NULL,

PRIMARY KEY (ID))

INSERT INTO #TempTable

SELECT 'Monday' UNION ALL

SELECT 'Tuesday' UNION ALL

SELECT 'Wednesday' UNION ALL

SELECT 'Thursday' UNION ALL

SELECT 'Friday'

DECLARE @DayName VARCHAR(100)

SET @DayName = 'Monday,Sunday,Friday'

SELECT

[WeekDay]

FROM

#TempTable AS tt

INNER JOIN

dbo.DelimitedSplit8K(@DayName,',') AS dsk

ON tt.WeekDay = dsk.Item

Output:

WeekDay

Monday

Friday