Jonathan Bourke

This is my blog. There are many like it, but this one is mine.

Using apply with table valued functions

comments mssql, apply, cross apply, outer apply, table-valued function edit

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.

Cross Apply Result Table

With that example in mind. If we now try the same query but instead use outer apply…

	-- 
	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.

Outer Apply Result Table

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.

Related Posts

Comments