在 SELECT 语句中使用 UDF [英] Using UDF in SELECT statement

问题描述

我为营业时间计算制作了一个用户定义的函数.

I made a user-define function for business hours calculation.

这是我的 UDF.

CREATE FUNCTION  fn_GetBusinessHour (@date datetime, @addHours int)
RETURNS datetime
AS
BEGIN 
    DECLARE @CalcuatedDate datetime;
    DECLARE @addDayCount int, @addHourCount int, @addMinCount int;

    SET @addDayCount = @addHours / 8.5;
    SET @addHourCount = @addHours - (@addDayCount * 8.5);
    SET @addMinCount =  @addHours - (@addDayCount * 8.5) - @addHourCount;

    IF(@addDayCount != 0) 
        SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date); 

    SET @CalcuatedDate = DATEADD(HH, @addHourCount, @CalcuatedDate);

    IF(@addMinCount != 0) 
        SET @CalcuatedDate = DATEADD(MM, @addMinCount, @CalcuatedDate); 

    RETURN @CalcuatedDate;
END

当我使用以下语句进行测试时，

When I test using following statement,

SELECT dbo.fn_GetBusinessHour(GETDATE(), 40)

它显示了正确的结果.

但是，我这样使用我的函数，

However, I use my function like this,

SELECT TicketID
     , DateTimeLogged --Type: Datetime
     , Priority       --Type: int
     , [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
  FROM TicketHeader

结果只显示NULL值.

the result shows only NULL value.

TicketID    DateTimeLogged  Priority    (No column name)
1   2011-07-04 11:26:19.510     30  NULL
2   2011-07-04 13:58:45.683     30  NULL
3   2011-07-05 10:09:16.923     10  NULL
4   2011-07-05 13:13:30.237     30  NULL
5   2011-07-05 16:50:34.033     20  NULL

我尝试了 CONVERT，因为当我给出值 40 时它起作用，但它也显示空值.

I tried CONVERT because it worked when I give a value 40 but it also shows null values.

SELECT TicketID
         , DateTimeLogged --Type: Datetime
         , Priority       --Type: int
         , [dbo].[fn_GetBusinessHour](DateTimeLogged, CONVERT(int, Priority))
      FROM TicketHeader

我该如何解决这个问题才能让我的 UDF 正常工作?为什么会发生这件事?我无法理解 Priority 和 40 之间有什么不同.

How can I fix this to work my UDF? Why this thing happen? I cannot understand what is different between Priority and 40.

提前致谢.

推荐答案

对于优先级 > 8.5 的值，这对我来说似乎很好:

For values of priority > 8.5, this seems to work fine for me:

DECLARE @t TABLE(TicketID INT, DateTImeLogged DATETIME, Priority INT);

INSERT @t SELECT 1,'20110704 11:26:19.510',30
UNION ALL SELECT 2,'20110704 13:58:45.683',30
UNION ALL SELECT 3,'20110705 10:09:16.923',10
UNION ALL SELECT 4,'20110705 13:13:30.237',30
UNION ALL SELECT 5,'20110705 16:50:34.033',20;

SELECT TicketID
     , DateTimeLogged --Type: Datetime
     , Priority       --Type: int
     , [dbo].[fn_GetBusinessHour](DateTimeLogged, Priority)
  FROM @t;

产量:

TicketID  DateTimeLogged           Priority  (No column name)
--------  -----------------------  --------  -----------------------
1         2011-07-04 11:26:19.510  30        2011-07-07 15:26:19.510
2         2011-07-04 13:58:45.683  30        2011-07-07 17:58:45.683
3         2011-07-05 10:09:16.923  10        2011-07-06 11:09:16.923
4         2011-07-05 13:13:30.237  30        2011-07-08 17:13:30.237
5         2011-07-05 16:50:34.033  20        2011-07-07 19:50:34.033

如果我添加另一行的优先级 <8.5，例如:

If I add another row with a Priority < 8.5, e.g.:

INSERT @t SELECT 6,'20110705 13:13:30.237',5;

然后将这一行添加到结果中:

Then this row is added to the result:

TicketID  DateTimeLogged           Priority  (No column name)
--------  -----------------------  --------  -----------------------
6         2011-07-05 13:13:30.237  5         NULL

换句话说，如果函数逻辑让@CalculatedDate 未赋值，则该函数将输出NULL，如果@addDayCount = 0，就会发生这种情况.在您说的函数中:

In other words, the function will output NULL if the function logic leaves @CalculatedDate unassigned, which will happen if @addDayCount = 0. In the function you say:

IF(@addDayCount != 0) 
    SET @CalcuatedDate = DATEADD(DD, @addDayCount, @date); 

因为@addDayCount 是一个 INT，试试这个:

Since @addDayCount is an INT, try this:

DECLARE @addDayCount INT;
SET @addDayCount = 5 / 8.5;
SELECT @addDayCount;

结果:

0

因此，因为@CalculatedDate 最初没有分配值，所以以下所有 DATEADD 操作都在执行 DATEADD(interval, number, NULL) ，但仍会产生 NULL.

So because @CalculatedDate isn't assigned a value initially, all of the following DATEADD operations are performing DATEADD(interval, number, NULL) which still yields NULL.

所以也许你需要为函数中的变量使用不同的数据类型...

So perhaps you need to use a different data type for the variables in the function...

这篇关于在 SELECT 语句中使用 UDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！