CODEWITHSUNDEEP
sqlsql-serverfunction
SLG333
SLG333

Reputation: 79

How can I create a function in SQL with 2 parameters?

I'm trying to create a function that SUMs the total number of minutes of actual duration for videos with a Finished status given a UserID as a parameter. This is what I have so far but I can't figure out how to add the parameter for the Finished status. This comes from a different table of Status that has a StatusID and StatusText. Or would I do a NOT NULL statement?

CREATE FUNCTION dbo.vc_VideoRunTime(@userID int)
RETURNS int AS 
BEGIN 
DECLARE @returnValue int
SELECT @returnValue = DATEDIFF (n, StartDateTime, EndDateTime) FROM vc_Video
WHERE vc_Video.vc_UserID = @userID
RETURN @returnValue
END
GO

Upvotes: 2

Views: 11648

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270993

If your finished status is represented as a NULL endtime, then your function is fine:

CREATE FUNCTION dbo.vc_VideoRunTime (
    @userID int
)
RETURNS int AS 
BEGIN 
    DECLARE @returnValue int;
    SELECT @returnValue = SUM(DATEDIFF(minute, v.StartDateTime, v.EndDateTime))
    FROM vc_Video v 
    WHERE v.vc_UserID = @userID ;

    RETURN @returnValue;
END;
GO

Why? The DATEDIFF() will return NULL if either argument is NULL. The SUM() will then return NULL.

Note that I changed the n to minute. This is much more readable. (I don't know anyone who uses the "n" option for "minute", it makes me think "nanosecond".)

If you actually want the function to return 0 in this case, then use COALESCE():

    RETURN COALESCE(@returnValue, 0);

Upvotes: 3

Related Questions