It also might be a better idea to change your @nd parameter to accept the numeric day of week instead of the week day name, and delimit that list by something other than a space (comma, semi-colon, pipe).
Using this calculation for the day of the week datediff(day, 0, DateOf) % 7 + 1 gives us Monday = 1, Tuesday = 2, Wednesday = 3, Thursday = 4, Friday = 5, Saturday = 6 and Sunday = 7 (ISO day of week number).
Here is that code:
Set Nocount On;
Declare @date1 date = '2020-01-01'
, @date2 date = getdate()
, @nd varchar(8000) = '1;2;6';
Set STATISTICS io, time On;
--==== Recursive Method
Print 'Recursive Method';
With AllDates As
(
Select @date1 As DateOf
Union All
Select dateadd(day, 1, DateOf)
From AllDates
Where DateOf < @date2
)
Select count(*) SumOfDays
From AllDates
Where Exists( Select 1
From STRING_SPLIT(@nd, ';')
Where datediff(day, 0, dateof) % 7 + 1 = Value)
Option (maxrecursion 0);
--==== inline-tally version
Print char(10) + 'Inline-Method';
With t(n)
As (
Select t.n
From (
Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
)
, dates (DateOf)
As (
Select Top (datediff(day, @date1, @date2) + 1)
dateadd(day, checksum(row_number() over(Order By @@spid)) - 1, @date1)
From t t1, t t2, t t3, t t4 -- 10000 rows = over 27 years of possible dates
)
Select SumOfDays = count(d.DateOf)
From dates d
Where Exists (Select *
From string_split(@nd, ';') ss
Where ss.value = datediff(day, 0, d.DateOf) % 7 + 1); -- datename(weekday, d.DateOf));
Set STATISTICS io, time Off;