In a stored procedure, I create a temp-table filled with a select which executes a function to return date recurrences.
The creation of my temp table looks like this:
BEGIN
insert into #tmp_recu
SELECT * FROM dbo.CrontabSchedule('0 0 * * *', '2017-2-1', '2017-2-28')
END
After creating this temp-table, I execute a query using this temp table like this:
Select * from mission
Cross Join #temp_recu
The problem is I'd like to replace the '0 0 * * *' in my temp-table creation by a field from mission table (field named recurrence), so how could I do that?
Thanks!
EDIT
Actually, in my query, I'd like to call the function 'CrontabSchedule' and put in parameter a field from 'mission' table like this:
select * from mission m
cross join select * from dbo.CronTabSchedule(mission.reccurence,'2017-1-1','2017-1-31')
It works when I called the function like this
select * from dbo.CronTabSchedule('0 0 * * *','2017-1-1','2017-1-31')
But when I replace '0 0 * * *' by 'Mission.recurrence' (which contains the recurrence pattern of each mission), I have an error:
The multi-part identifier "Mission.recurrence" could not be bound.
CrontabSchedule code:
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER FUNCTION [dbo].[CrontabSchedule](@Expression [nvarchar](100), @Start[datetime], @End [datetime])
RETURNS TABLE (
[Occurrence] [datetime] NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [NCrontabSQL].[NContab.SQL.SqlCrontab].[GetOccurrences]
The function return a table with one column named 'Occurence' and contains a list of dates.
Pretty vague question here but I am assuming that CronTabSchedule must be a table valued function (hopefully an inline version but that is another topic). If I am correct you could use CROSS APPLY for this quite easily.
select *
from mission m
cross apply dbo.CronTabSchedule(m.reccurence,'2017-1-1','2017-1-31') cts