sqlsql-serverstored-procedurestemp-tables

Stored proc temporary table with parameters


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.


Solution

  • 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