Join to Parametrized Table Function - Cross Apply and Outer Apply in SQL Server 2005
January 6th, 2008 by Andrew Chen
I know for a long time that table function can be used just like a physical table to participate in a join in SQL query. An example is like the following
select B.* from tableA as A inner join dbo.tablefunctionB() as B on A.id = B.id
In the above query dbo.tablefunctionB() is a table function that returns
select B.* from tableA as A inner join dbo.tablefunctionB(A.FristName) as B on A.id = B.id
It will fail and SQL server will tell you there is syntex error in the query. You can only pass a value or variable to the table function as parameter and you can not pass a column as the paramter. I was thinking at that time that it would be very nice if SQL server allow us do this. Now with SQL Server 2005 we can do this with cross apply or outer apply operators. You just need to change the inner join to cross apply and move the join condition to a where statement like this.
select B.* from tableA as A cross apply dbo.tablefunctionB(A.FirstName) as B where A.id = B.id
For left outer join you may use “outer apply” operator.
Related Posts:
(6 votes, average: 4.33 out of 5)
Loading ...
still not working
my command is
select *
from SLSINVOICEdt as dt
inner join SLSINVOICEHD as hd on dt.invoiceno=hd.INVOICENO
inner join MSCUST as cust on cust.CUSTCODE=hd.CUSTCODE
cross apply F_GATTAX(hd.CUSTCODE,hd.INVOICENO,cust.TAXFLAG) as tot
where tot.invoiceno=hd.INVOICENO
and i still got error “Incorrect syntax near ‘.’”
thanks
I had same error as you “Incorrect syntax near ‘.’” and after changing the database compatibility level to SQL Server 2005(take a look at Database Properties ) the query worked fine.
It looks like an error within your function F_GATTAX
just what i wanted cheers!
Very nice post !!!
Thank U.
Thanks a lot, just what i needed.
Excellent post!!! Thanks for your insight!
Thank you so much for this. Was banging my head against the wall with “multi-part identifier not found” error.
Thanks a lot for the nice post..it definitely help me to solve the problem.
Hello people,
I tried to use the examples and got in trouble because “cross apply” only works on SQL 90 compatibility mode – not in SQL 2005 with mode 80 enabled (my case). Since I can’t change the database, I create the following work around:
– Code
USE tempdb – tempdb is a native SQL 2005 DB
SELECT
tb.field1,
tb.field2,
fc.*
FROM #tempTB as tb
CROSS APPLY OficialDB_Compatibility80.dbo.myUDF (tb.field1, tb.field2, tb.field3, @dinamicVAR) AS fc
USE OficialDB_Compatibility80
– Code
This worked for me. Good lock!
Bye
Josué Monteiro Viana
Excellent.
Thanks!
Nice one. This has saved me an awful lot of time.
thanks man
I wonder why this following doesn’t work in sql-server2008, then?
select * From sys.databases AS d CROSS APPLY sys.dm_io_virtual_file_stats(d.database_id, null) as fn
an ideas?
Just an FYI:
It turns out the function to which we’re cross applying cannot be an inline function… it has to return a table with… it’s a known bug apparently:
http://connect.microsoft.com/SQLServer/feedback/details/241325/cross-apply-and-dynamic-management-functions
anyways, your post definitely put me in the right direction.
cheers!