SQL Server scalar function with table

Question

I have a user defined function like this:

CREATE FUNCTION getMinutesWork
(
    @startT datetime,
    @endT datetime
)
RETURNS int
As
Begin
    bla bla
    bla bla
RETURN @totalMinute
End

It calculates total minutes between two dates. I want to use it with a table like this:

WorkerID |LastName| FirstName  |  StartDate          | EndDate
   1     |   JOHN |    SNOW    |1979-12-26 02:47:00  | 1999-02-16 12:44:00
   2     |  ...   |   ...      |   .... .            |    .....

StartDate and EndDate are inputs. But i couldnt figure out how ill turn my function table valued one

I'm not following... what is your question? Are you wanting to know how to pass in the values from your table? — Siyual
– Siyual, Commented Jul 28, 2015 at 13:41
@siyual Yes. I want to use this function for 3-5k people and see each total minutes on a table. — calcuku
– calcuku, Commented Jul 28, 2015 at 13:43
I think you are confusing a table valued function, with calling a scalar function using values from a table. As far as I can tell your query would be ---- SELECT t.WorkerID, t.LastName, t.Firstname, t.StartDate, t.EndDate, MinutesWork = getMinutesWork(t.StartDate, t.EndDate) FROM YourTable AS t — GarethD
– GarethD, Commented Jul 28, 2015 at 13:45
Performance wise you would be better off if you can use a inline table valued function instead of a scalar function. — Sean Lange
– Sean Lange, Commented Jul 28, 2015 at 14:09

Gordon Linoff · Accepted Answer · 2015-07-28 13:44:26Z

4

Just call your function in the select:

select t.*,
       dbo.getMinutesWork(StartDate, EndDate)
from table t;

answered Jul 28, 2015 at 13:44

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

SQL Server scalar function with table

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