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?I would create a table variable with one field that will hold the date. The do a loop to populate it. I'd make sure @.startdate and @.enddate have the time stripped off. Not tested, but should work with minor tweaks.

set @.date = @.startdate
set @.x = datediff(d, @.startdate, @.enddate)
set @.y = 0
While @.y <= @.x
 Begin
 insert into @.table (datefield) values (dateadd(d, @.y, @.startdate))
 set @.y = @.y + 1
 End

|||ooo that's a nice loop. :)

retriving data from 2 views by the value in a textbox

i have a textbox which a user enters a numeric value

i want it to use SqlDataSource and check if the value exists in any of the tables.

in my text box the users would enter starting from '100000' or '200000'

i want it to check the view that starts the # with '100000' and 2ed view starts '200000'

With this i can check in one of the tables and make the selection.

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:imacstestConnectionString %>"

SelectCommand="SELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber)">

<SelectParameters>

<asp:ControlParameterControlID="txtReport"Name="ReportNumber"PropertyName="Text"

Type="String"/>

</SelectParameters>

</asp:SqlDataSource>

How can i make this possible ?

i was thinking putting a second sqldatasource and have that check the second view but how can i make the textbox goto the correct selectcommand ?

You'll need to do a conditional if in your select command:

IF @.ReportNumber > 10000 BEGINSELECT [ReportNumber] FROM [AppraisalSummaryBlue] WHERE ([ReportNumber] = @.ReportNumber) END ELSE BEGINSELECT [ReportNumber] FROM [AppraisalSummaryTHEOTHERTABLE] WHERE ([ReportNumber] = @.ReportNumber) END