将一系列日期传递给SQL SERVER中的标量用户定义函数 [英] Pass a range of dates to a scalar user defined function in SQL SERVER

问题描述

我有一个以下UDF函数，它接受一个日期参数并返回4个字段。

 选择 a，b，c，d 来自 [dbo]。[udf_ConfReportedDate]（{D '  2017-05-31'}）

我想传递一系列日期，从2017-05-31到2017-06-30一个接一个地附加我的输出。我怎样才能实现这一目标？



请注意我的UDF一次只能有一个日期。



我尝试过：



DECLARE @Start_Date DATETIME ='2016-06-01'

DECLARE @End_Date DATETIME ='2016-12-31'



从[dbo]中选择*。[udf_ConfReportedDate]（{D'2017-05-31'}）



但它只给出了一个日期。我可以使用while循环或CTE或其他东西传递日期吗？

解决方案

这将为您提供所需日期的有效计数表，只需将函数调用到SELECT中即可你应该没问题：

 DECLARE @toDate DATE 
 DECLARE @fromDate DATE 
 SET @fromDate ='2017 -05-31'
 SET @ toDate ='2017-06-30'
 
; WITH cte（Date）AS 
（
 SELECT @fromDate 
 UNION ALL 
 SELECT DATEADD（DAY，1，Date）
 FROM cte 
 WHERE Date< @toDate 
）
 SELECT * FROM cte 

Hi, I have a following UDF function that takes a date parameter and returns 4 fields.

select a,b,c,d from [dbo].[udf_ConfReportedDate] ({D'2017-05-31'})

I would like to pass a range of dates say from 2017-05-31 to 2017-06-30 one by one and append my output. How can I achieve that?

Please note that my UDF can only take one date at a time.

What I have tried:

DECLARE @Start_Date DATETIME ='2016-06-01'
DECLARE @End_Date DATETIME ='2016-12-31'

Select * from [dbo].[udf_ConfReportedDate] ({D'2017-05-31'})

But it just gives for one date. Can I pass dates using while loop or CTE or something?

解决方案

This will give you an effective tally table of the dates required, just work your function call into the SELECT and you should be okay:

DECLARE @toDate DATE
DECLARE @fromDate DATE
SET @fromDate = '2017-05-31'
SET @toDate='2017-06-30'
 
;WITH cte (Date)  AS
(
    SELECT @fromDate 
        UNION ALL
    SELECT DATEADD(DAY, 1, Date)
        FROM cte
        WHERE Date < @toDate
)
SELECT * FROM cte 

这篇关于将一系列日期传递给SQL SERVER中的标量用户定义函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！