Siusic Dot Com

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

a table with an id column. I encountered some cases before that the table function took a parameter and I had a list of values in another table that I wanted to pass to the table function as paramter. Assuming that the table function dbo.tablefunctionB takes a string (First name) as parameter and tableA is a table that holds all the first names that I want to pass to the table function. So I tried the following query

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:

List All Permissions a User Has in SQL Server Database and Error 4064

New Answer on How to Defragment a Table Being Used 24/7

How to Script Out all The Foreign Keys of a Table

Find the First Occurrence of a Pattern Using Regular Expression in T-SQL

A Fast Way to Update Large Amount of Data in SQL Server With Select Into Statement