带参数的存储过程临时表

Hello in a stored procedure, i create a temp-table filled with a select wich execute a function to return date recurrences.

你好在一个存储过程中，我创建了一个temp-table，其中包含一个select，它执行一个函数来返回日期重复。

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 remplace the '0 0 * * *' in my temp-table creation by a field from mission table (field named recurrence), so how could i do that?

问题是我想通过任务表中的一个字段（名为recurrence的字段）在我的临时表创建中重新设置'0 0 * * *'，那么我怎么能这样做呢？

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 :

实际上，在我的查询中，我想调用函数'CrontabSchedule'并从'mission'表中输入参数字段，如下所示：

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 :

但是当我用'Mission.recurrence'（包含每个任务的重复模式）替换'0 0 * * *'时，我有一个错误：

The multi-part identifier "Mission.recurrence" could not be bound.

无法绑定多部分标识符“Mission.recurrence”。

CrontabSchedule code :

CrontabSchedule代码：

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 à list of dates.

该函数返回一个表，其中一列名为“Occurence”，并包含à日期列表。

1 个解决方案

#1

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.

这里的问题很模糊，但我假设CronTabSchedule必须是一个表值函数（希望是内联版本，但这是另一个主题）。如果我是正确的，你可以很容易地使用CROSS APPLY。

select * 
from mission m
cross apply dbo.CronTabSchedule(m.reccurence,'2017-1-1','2017-1-31') cts