Using apply with table valued functions
So I got asked the other day whether it was possible to call a table-valued function for each record returned from another table-valued function, in one statement.
It is possible, thanks to the
apply operator - see MSDN for more.
So if we take a contrived example of a parent with children.
use JB; IF OBJECT_ID (N'dbo.fnGetParent', N'TF') IS NOT NULL DROP FUNCTION dbo.fnGetParent; GO create function dbo.fnGetParent() returns @parent table ( PersonId int primary key not null, FirstName nvarchar(255) not null, IsParent bit not null ) as begin insert into @parent values (1, 'Jim', 1) insert into @parent values (2, 'Sarah', 1) insert into @parent values (3, 'Fred', 0) return end; go IF OBJECT_ID (N'dbo.fnGetKids', N'TF') IS NOT NULL DROP FUNCTION dbo.fnGetKids; GO create function dbo.fnGetKids (@parentId int) returns @child table ( ChildId int primary key not null, ParentId int not null, FirstName nvarchar(255) not null ) as begin insert into @child values (1, 1, 'Zelda') insert into @child values (2, 2, 'Chris') insert into @child values (3, 1, 'Mandy') delete from @child where ParentId <> @parentId return end; go
If we try a simple query where we get all the parents and
cross apply that with children…
-- select p.PersonId, p.FirstName, k.FirstName, k.ParentId from dbo.fnGetParent() p cross apply dbo.fnGetKids(p.Personid) k
we will get all records from left side that have records on right, as shown in the screen grab.
With that example in mind. If we now try the same query but instead use
-- select p.PersonId, p.FirstName, k.FirstName, k.ParentId from dbo.fnGetParent() p outer apply dbo.fnGetKids(p.Personid) k
We now get all records from left regardless of whether they have records on right.
So there you have it. A very simple, easy to follow (imho) example showing how to use the
apply operator with table valued functions.
I’m not going to go much more the
apply operator as others have written better articles, so here’s Rob.