SQL Server: run function for each row based on the provided row value

Question

I am still learning SQL and I was wandering how can I pass a row value into a function in a cross apply query.

Assuming that this is my first table

SELECT 
    [Project], [emp_id],
    [Allocation_start], [Allocation_end]
FROM
    First_table

I have a function that takes 2 arguments - start and end date, and returns dates split by the week intervals...

Example :

select * from [udf_GetIntervals]('2017-01-01','2017-01-30')

Data:

dt_start    dt_end
----------------------
2016-12-26  2017-01-01 
2017-01-02  2017-01-08 
2017-01-09  2017-01-15 
2017-01-16  2017-01-22 
2017-01-23  2017-01-29 
2017-01-30  2017-02-05

What I did was I run the mentioned function with dates starting from the 1st July to the 30th December and stored it in virtual table and then used cross apply with that table.

select * 
from [First_table] 
cross apply
    (select * from #temp) b

Which works, but it works independently of the first_tables start and end date always returning all weeks per 1 project record from first table.

I was wondering, how can i do this, using first_tables start_date and end_date values, so that my cross apply returns only records with the week_intervals contained in the rows start/end date.

I would appreciate any hints on the matter.

What does your `First_table` contain and what is your expected result? — Viki888, Nov 24 '16 at 07:29
Please explain with example of data in first table and final result. — Gaurav Gupta, Nov 24 '16 at 07:30
Firstly, good on you for writing a very well thought out and detailed question. If you are learning SQL take some advice: it's not like programming where you write a function for everything and call the function. You should think about doing a 'set based' solution. In this case it means creating a calendar table that has all the possible values, and joining to it. When you use functions, they work fine for 100 rows, 1000 rows, 10,000 rows but eventually as the row count increases, the performance just drops through the floor and you're stuck rewriting something in a hurry. — Nick.Mc, Nov 24 '16 at 07:55

score 12 · Accepted Answer · answered Nov 24 '16 at 07:47

You can use APPLY do perform row-wise actions. CROSS APPLY behaves similar to an INNER JOIN, while OUTER APPLY is rather like a LEFT JOIN.

A Table-Valued-Function returns a table. You must provide an alias and inlcude this resultset into your column list:

SELECT [Project]
      ,[emp_id]
      ,[Allocation_start]
      ,[Allocation_end]
      ,WeekDates.*
From First_table
CROSS APPLY dbo.[udf_GetIntervals]([Allocation_start],[Allocation_end]) AS WeekDates

David דודו Markovitz · Answer 2 · 2016-11-24T07:48:28.920

5

select * 
from   First_table cross apply [udf_GetIntervals]([Allocation_start],[Allocation_end])

edited Nov 24 '16 at 07:48

answered Nov 24 '16 at 07:36

David דודו Markovitz

42,900
6
64
88

SQL Server: run function for each row based on the provided row value

2 Answers2

Linked