Table valued function using cross apply

able Valued Function using cross apply

If you want to pass the parameter of table column in the table value function we can use the cross apply. Also if you want to pass dynamic value to table valued function we can use the cross apply

Example

Consider a table with employee leave status, which has employee id, from date, to date and approved. We can use cross apply to return a particular date of the employee leave status using table valued function as explained in below code.

Now, create a table leave and insert records

1. createtable leave
2. (
3. LeaveId INT IDENTITY(1,1)
4. ,EmployeeId INT
5. ,FromDate Datetime
6. ,ToDate Datetime
7. ,[Status] VARCHAR(50)
8. )
9. Go
10. insertinto leave values(1,'2011-10-25','2011-10-27','Approved')
11. insertinto leave values(2,'2011-10-26','2011-10-27','Approved')
12. insertinto leave values(3,'2011-10-27','2011-10-27','Rejected')
13. insertinto leave values(1,'2011-11-01','2011-11-01','Approved')

The result of the leave table is as follows

LEAVE ID	EMPLOYEEID	FROM DATE	TO DATE	STATUS
1	1	2011-10-25	2011-10-27	Approved
2	2	2011-10-26	2011-10-27	Approved
3	3	2011-10-27	2011-10-27	Rejected
4	1	2011-11-01	2011-11-01	Approved

But as per our requirement we need a result as shown below

Employee Id	Date	Status
1	011-10-25 00:00:00.000	Approved
1	2011-10-26 00:00:00.000	Approved
1	2011-10-27 00:00:00.000	Approved
2	2011-10-26 00:00:00.000	Approved
2	2011-10-27 00:00:00.000	Approved
3	2011-10-27 00:00:00.000	Rejected
1	2011-11-01 00:00:00.000	Approved

We can use Table valued function with CROSS APPLY to resolve the issue.

Step: 1

The first step is to create function

1. CREATEFUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
2. returnstableas
3. return (
4. WITH date_range (startdate) AS (
5. select @startdate
6. UNIONALLSELECT DATEADD(DAY, 1, startdate)
7. FROM date_range
8. WHERE DATEADD(DAY, 1, startdate) <= @enddate
9. )
10. SELECT startdate FROM date_range
11. );

The above function will retrieve dates between two dates.

1. SELECT * FROM dbo.ExplodeDates ('2011-10-25', '2011-10-27')

If we run the above select query it will display result as follows

Start date
2011-10-25 00:00:00.000
2011-10-26 00:00:00.000
2011-10-27 00:00:00.000

Step: 2

We need to use cross apply to pass data to the function to get the specified result. It won’t work if we pass an argument statically.

1. GO
2. SELECT EmployeeId, startdate, [Status] FROM leave
3. CROSS APPLY
4. DBO.ExplodeDates(leave.FromDate, leave.ToDate)
5. GO

If you execute the above the result is achived.

Source : https://sql-programmers.com/table-valued-function-using-cross-apply