Note: This issue appears to be limited to SQL Server 2005 SP2
I have a common table expression that iterates over a series of dates. I need to reference each of these dates in a call to a table-valued function. Something like this below, which gives a syntax error around the second parameter of the call to GetMyData. If I use another value, such as @END_DATE, it works. This is the error:
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near '.'.
Is there another way to do this, other than using a cursor?
if object_id (N'dbo.GetMyData', N'TF') is not null
drop function dbo.GetMyData
go
create function GetMyData(@d datetime)
returns @t table (part varchar)
as
begin
insert @t select 'a'
insert @t select 'b'
insert @t select 'c'
return
end
go
declare @START_DATE datetime
declare @END_DATE datetime
set @START_DATE = '4/1/2007'
set @END_DATE = '4/11/2007'
with dates_in_range([date]) as
(
select [date] = @START_DATE
union all select [date] = [date] + 1
from dates_in_range
where [date] < dateadd(dd,0, datediff(dd,0,@END_DATE))
)
select d.[date], c.*
from dates_in_range d
cross apply dbo.GetMyData(d.[date]) c