Select statements included within a function cannot return data to a client. FUNCTION SQL SERVER

Question

CREATE FUNCTION Testing(@d1 DATE,@d2 DATE,@nd VARCHAR(MAX))
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
;with AllDates AS
(
SELECT @d1 AS DateOf
UNION ALL
SELECT DateAdd(day,1,DateOf)
FROM AllDates
WHERE DateOf<@d2
) select COUNT(*) SumOfDays FROM AllDates
WHERE NOT EXISTS(SELECT 1
FROM STRING_SPLIT(@nd,' ')
WHERE DATENAME(weekday,dateof) = value)

END;

Answer 1

You are trying to create a scalar function (RETURNS int). Such can only return one value using the RETURN command inside your function code.

I have a feeling that you want to create a table function, to return a table (like a parameterized view, if such existed). Then you need to change your code to (untested):

CREATE FUNCTION dbo.Testing(@d1 DATE, @d2 DATE, @nd VARCHAR(MAX))
RETURNS TABLE
AS
RETURN(
    WITH AllDates AS
        (
            SELECT @d1 AS DateOf
            UNION ALL
            SELECT DateAdd(day,1,DateOf)
            FROM AllDates
            WHERE DateOf<@d2
        ) 
    select COUNT(*) SumOfDays 
    FROM AllDates
    WHERE NOT EXISTS(SELECT 1
    FROM STRING_SPLIT(@nd,' ')
    WHERE DATENAME(weekday,dateof) = value)
    )