July 26, 2011 at 6:37 am
I have a simple question?
can i join a table valued function with a table.
if so how we can achieve this.
July 26, 2011 at 6:43 am
This should help
July 26, 2011 at 6:44 am
You can join them if you query the TVF in a derived table. More commonly, you can Apply them. For details, look up "Cross Apply" and "Outer Apply" in MSDN or Books Online.
Here's a sample of what Cross Apply looks like:
select *
from dbo.MyTable
Cross Apply dbo.MyTVF(MyTable.Col1);
I'm assuming from the forum you posted in that you're using SQL 2008. Cross/Outer Apply works in SQL 2005 and later. If you're actually using SQL 2000 or earlier, you'll need to do this a different way.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 26, 2011 at 7:28 am
Simple answer is Yes you can join to TVF (actually is main reason it was designed for).
Example:
CREATE FUNCTION fn_List ( @p1 int ) RETURNS TABLE
AS
RETURN
(
SELECT @p1 AS a union SELECT @p1 +1 AS a union SELECT @p1 +2 AS a
)
GO
declare @t table (c int)
insert @t (c) values(1),(2),(3),(4)
select *
from @t t
left join dbo.fn_List(1) f on t.c = f.a
July 26, 2011 at 7:44 am
Thank u all....
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply