Return All Months Within A Range Of Dates

I currently have a stored procedure that returns a list of dates based on a date range a user enters.








CREATE PROCEDURE sp_GetContactScheduleDates


@MonthFrom int,


@YearFrom int,


@MonthTo int,


@YearTo int,


@DaysInMonth int


AS


Select distinct s.ScheduleMonth, s.ScheduleYear


From OnCall_Schedules s


Where CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)


>= CAST(cast(@MonthFrom as nvarchar) + '/' + cast('01' as nvarchar) + '/' + cast(@YearFrom as nvarchar) as smalldatetime)


And CAST(cast(s.ScheduleMonth as nvarchar) + '/' + cast(s.ScheduleDate as nvarchar) + '/' + cast(s.ScheduleYear as nvarchar) as smalldatetime)


<= CAST(cast(@MonthTo as nvarchar) + '/' + cast(@DaysInMonth as nvarchar) + '/' + cast(@YearTo as nvarchar) as smalldatetime)


Order by s.ScheduleYear, s.ScheduleMonth


GO








However, this only brings back those dates that are in the table. I need to get ALL dates within the range.





For example, the OnCall_Schedules table contains schedules that are saved by the user. If no one has ever saved a schedule at any time in May 2004 and the range of dates entered is January 2004 to June 2004, then May 2004 will not be returned. I need to get back all dates within that range regardless if it has something scheduled or not. How can this be done?





Note - I do not want to set up any dummy records or create a table with valid dates as the user will be allowed to choose any range of dates and we do not want to have to maintain anything.





Can some sort of function be used? What would the code look like?