Table row generation based on two date difference

Recently i faced a situation where i was supposed to generate a table records based on the difference of two date parameter values.


After searching a lot on internet, i came up with a solution to my problem using Common Table expression feature of SQL server.


MSDN definition states:- 
A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a 
single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. 
Unlike a derived table, a CTE can be self-referencing and 
can be referenced multiple times in the same query.


The query example for generating the table:-


BEGIN
     Declare @FromDate as DateTime
     Declare @ToDate as DateTime   
     Set @FromDate = cast('09/01/2011' as datetime)
     Set @ToDate = cast('09/15/2011' as datetime)

     ;With cteTemp as (
               select 1 i, @FromDate As FromDate
               Union All
               select i+1, DateAdd(Day,i,@FromDate) 
               From cteTemp
               Where DateAdd(Day,i,@FromDate)<=@ToDate 
         )   
       select * from cteTemp
END


Above mentioned query generates 15 records based on the difference between the date values. As Common table expression works in recursive way, same table name is used inside the query for looping purpose.