The standard way to solve such puzzles is to have Calendar and Numbers tables in the database. If you have such tables, then the solution becomes trivial

select S.*, Cal.TheDate from dbo.SourceData S inner join dbo.Calendar Cal on Cal.TheDate between S.StartDate and S.EndDate

>Haven't done one of these in a while.
>
>Recently someone asked me about exploding out a date range by user.
>
>For instance, suppose you had the following table definition and two sample rows:
>
>

>create table dbo.SourceData (Name varchar(100), StartDate date, EndDate date)	
>
>insert into dbo.SourceData values
>    ('Kevin Goff', '9-12-2016', '9-12-2016'), 
>    ('Katy Goff','9-12-2016', '9-15-2016') 
>

>
>So I have a date range of 9-12 to 9-12 (one day), and Katy has a date range of 9-12 to 9-15 (4 days).
>
>I want to generate a result set of 5 rows...one for me and four for Katy, with the corresponding dates. In reality, the sourceData table could have thousands of rows.
>
>Absolute best performance isn't paramount, since this routine would only run once a day, but obviously it could run more frequently down the road.
>
>Here was my solution...I've had to do this before to populate data marts. I know that recursion allows me to generate a range, but in order to use a CROSS APPLY to apply the routine across many rows, I've only ever been able to do it with a table function.
>
>So here was my solution:
>
>

>CREATE FUNCTION dbo.ExplodeDates
>( @StartDate date, @EndDate date)
>returns table
>as 
>return (
>    with tempcte as
>                (select @StartDate as ActualDate
>                      union all             -- CTE recursive loop
>                   select dateadd(d,1,ActualDate) from tempcte
>                           where dateadd(d,1,ActualDate) <= @EndDate )
>        select * from tempcte )
>
>go
>
>
>select Name, ActualDate from SourceData
>   CROSS APPLY dbo.ExplodeDates ( SourceData.StartDate, SourceData.EndDate)
>        OPTION (MAXRECURSION 32767)
>

>
>This works, and generates the following results:
>
>

>Name	     ActualDate
>Kevin Goff       2016-09-12
>Katy Goff       2016-09-12
>Katy Goff       2016-09-13
>Katy Goff       2016-09-14
>Katy Goff       2016-09-15
>

>
>Again, this works - though it doesn't seem 100% optimal. Admittedly, I've never spent any serious time thinking about ways to optimize it. But just curious if anyone had any thoughts.
>
>Thanks