Table valued function using cross apply - SQL Programmers

Table 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

create table leave
(
LeaveId INT IDENTITY(1,1)
,EmployeeId INT
,FromDate Datetime
,ToDate Datetime
,[Status] VARCHAR(50)
)
Go
insert into leave values(1,'2011-10-25','2011-10-27','Approved')
insert into leave values(2,'2011-10-26','2011-10-27','Approved')
insert into leave values(3,'2011-10-27','2011-10-27','Rejected')
insert into leave values(1,'2011-11-01','2011-11-01','Approved')

The result of the leave table is as follows

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

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

Step: 1

The first step is to create function

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
WITH date_range (startdate) AS (
  select @startdate
  UNION ALL SELECT DATEADD(DAY, 1, startdate)
            FROM date_range
            WHERE DATEADD(DAY, 1, startdate) <= @enddate
            )
SELECT  startdate FROM date_range 
);

The above function will retrieve dates between two dates.

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

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

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.

GO
SELECT EmployeeId, startdate, [Status] FROM  leave
CROSS APPLY
DBO.ExplodeDates(leave.FromDate, leave.ToDate)
GO

If you execute the above the result is achived.