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

Question

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

So what is missing in the above, another input parameter? Or, do you want to return more than one value? — Tim Biegeleisen
– Tim Biegeleisen, Commented Sep 3, 2018 at 2:32
I only need to return the total minutes but some videos aren't finished yet so the results are NULL and I'm trying to remove the NULL results. I'm just not sure how to add input parameter for the finished status to only get return values with total minutes — SLG333
– SLG333, Commented Sep 3, 2018 at 2:43
you wanted the function to return total number of minutes and also finished status ? — Squirrel
– Squirrel, Commented Sep 3, 2018 at 2:52
why then not just modify your WHERE clause like so WHERE vc_Video.vc_UserID = @userID AND vc_Video.EndDateTime IS NOT NULL clause? — Carlo Bos
– Carlo Bos, Commented Sep 3, 2018 at 3:17

Gordon Linoff · Accepted Answer · 2018-09-03 05:43:09Z

3

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);

answered Sep 3, 2018 at 5:43

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related