Node17
Node17

Reputation: 537

Loop a table valued function sql

I have a table valued function which returns the boss of a person for a given day.

dbo.Bosses(@empId, @date)

How do I loop through this function with information from another?

I.e, I want to use Table B that has a date and employee Id and I want to use them as arguments to find all the bosses for each day inputted in Table B

Table B
EmpId     int
hours     float
day       datetime
creator   int

Upvotes: 2

Views: 2075

Answers (2)

MatBailie
MatBailie

Reputation: 86706

Assuming you have SQL Server 2005+

SELECT
  *
FROM
  TableB
CROSS APPLY
  dbo.Bosses(TableB.EmpID, TableB.day) AS bosses

CROSS APPLY will only return results where the Bosses function returns results. Similary to an INNER JOIN.

OUTER APPLY will return results for every entry in TableB, similar to a LEFT JOIN.

Upvotes: 4

a1ex07
a1ex07

Reputation: 37354

You need to use APPLY operator (CROSS or OUTER, the former is similar to INNER JOIN, the later - to LEFT JOIN):

SELECT b.*, a.*
FROM table_b b
CROSS APPLY dbo.Bosses(b.emp_id, b.emp_date)a

Upvotes: 3

Related Questions