I'm posting the function again with some comments. Hopefully those comments will help you understand how the query is working. I should strongly recommend that you read the following article on Tally Tables which is the base for this code: http://dwaincsql.com/2014/03/27/tally-tables-in-t-sql/
CREATE FUNCTION Generate_Dates(
@StartDate date,
@EndDate date
)RETURNS TABLE WITH SCHEMABINDING
AS
RETURN
WITH
E(n) AS(
--Create 10 rows
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
--Create 10*10 = 100 rows
SELECT a.n FROM E a, E b
),
E4(n) AS(
--Create 100*100 = 10,000 rows
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 --Add row for initial value (start date without change)
UNION ALL
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate)) --Limit the rows to the number of days in the range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n --Assign consecutive numbers to the rows from 1 to N
FROM E4
)
SELECT DATEADD( dd, n, @StartDate) AS CalDate --Add days to the start date
FROM cteTally;
Now, the problem that you're having, is because the DATEDIFF is returning an invalid value to the TOP clause. This could be cause by a NULL value which you would have to validate to prevent errors. Be aware that an incorrect range where the start date is greater than the end date would also cause errors.
Play with the function and understand how does it works. Run the CTE one step at a time to see how the result is being created. Here's the function in the form of an independent query.
DECLARE
@StartDate date,
@EndDate date
WITH
E(n) AS(
--Create 10 rows
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
--Create 10*10 = 100 rows
SELECT a.n FROM E a, E b
),
E4(n) AS(
--Create 100*100 = 10,000 rows
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 --Add row for initial value (start date without change)
UNION ALL
SELECT TOP(DATEDIFF(DD, @StartDate, @EndDate)) --Limit the rows to the number of days in the range
ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n --Assign consecutive numbers to the rows from 1 to N
FROM E4
)
SELECT DATEADD( dd, n, @StartDate) AS CalDate --Add days to the start date
FROM cteTally;