如何编写用户定义函数？

I would like to write this as a user defined function:

我想把它写成用户定义的函数:

private double Score(Story s){            
               DateTime now = DateTime.Now;            
               TimeSpan elapsed = now.Subtract(s.PostedOn);            
               double daysAgo = elapsed.TotalDays;            
               return s.Votes.Count + s.Comments.Count - daysAgo;       
                              }

Is this possible to do with a UDF?

这可能与UDF有关吗?

2 个解决方案

#1

You can, but if you're using SQL Server 2000, you'll have to pass in the value of "now"--UDFs can't generate any non-deterministic values themselves in SQL Server 2000.

您可以,但如果您使用的是SQL Server 2000,则必须传入“now”的值 - UDF不能在SQL Server 2000中自行生成任何非确定性值。

This untested stab at it might be close:

这种未经测试的刺戳可能会很接近:

CREATE FUNCTION dbo.GetStoryScore (
    @Now DATETIME, @Posted DATETIME, @Votes INT, @Comments INT
) RETURNS FLOAT AS
BEGIN

  RETURN @Votes + @Comments - DATEDIFF(HOUR, @Posted, @Now)/24.0

END

Example usage:

SELECT S.ID, dbo.GetStoryScore(GETDATE(), S.Posted, S.Votes, S.Comments) AS Score
FROM Stories AS S
WHERE ...

Notes:

The datediff is performed in hours (not days) because the integer result gives you a little more precision when you use finer units.

日期是在几小时(而不是几天)内执行的,因为当您使用更精细的单位时,整数结果会给您更高的精度。
I passed in all the values because I've found lookups within functions to be a really, really bad thing for performance.

我传递了所有的值,因为我发现函数中的查找对于性能来说是一个非常非常糟糕的事情。
When referenced in SQL, don't forget the dbo. in front of the function name.

在SQL中引用时,不要忘记dbo。在函数名称前面。
If you're using SQL Server 2005, you can remove the @Now variable and use GETDATE() inline, instead.

如果您使用的是SQL Server 2005,则可以删除@Now变量并使用内联的GETDATE()。

#2

Something like the following I'd imagine:

像我想象的那样:

CREATE FUNCTION [dbo].[Score]
(@storyid int)
RETURNS float
AS
BEGIN
    DECLARE @ret float;
    DECLARE @elapsed float;

    SELECT @elapsed = DATEDIFF(n, getdate(), postedon) / 1440
    FROM stories WHERE storyid = @storyid;

    SET @ret = ((SELECT count(voteid) FROM votes WHERE storyid = @storyid) +
                    (SELECT count(commentid) FROM comments WHERE storyid = @storyid) -
                    @elapsed);
    RETURN @ret;
END

#1