How do I write a User Defined Function?

Question

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?

Answer 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.

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.

• If you're using SQL Server 2005, you can remove the @Now variable and use GETDATE() inline, instead.

Answer 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