table returning udf join

Question

table returning udf join

B Mcliarg

Newbie

Points: 9
More actions
December 23, 2004 at 11:29 am

#162093

I have a udf that takes a parm and returns a table
I am trying to do something like this
select a.id from a
join dbo.myTableGeneratingUdf(a.id) b on a.id = b.id
but sql server doesnt like it.

In english, I would like to apply the table udf to all entires in a table and get a result set.
What the bleep am I doing wrong?

thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

This is a common misunderstanding of the Table valued Functions.

Supose your function returns Different result sets for different arguments HOW is the JOIN you are trying to accomplish going to be resolved?

Join to the first, second, third .... resulset or it would be any combination of those? ... Of Course SQL has to complain about that.

As long as you are able to retrieve a SINGLE resulset the join will happen normally so your solution will be ... rethink / refactor the function so that you can accomplish the goal.

HTH

* Noel

Dave-3000 SSCertifiable Points: 6186 More actions · Answer 2

What if you used a derived table instead of a udf?

select a.id from MyTable AS a

JOIN

(SELECT MyLastTable.id FROM MyNextTable JOIN MyLastTable ON MyNextTable.colb = MyLastTable.colb) AS b

ON a.id = b.id

I don't know what goes on in your udf, so maybe just a result set to join on is not what you need.

Luck, Dave

There is no "i" in team, but idiot has two.

michanne SSCrazy Points: 2329 More actions · Answer 3

Your syntax looks fine. [id] must be in the result set in order to do a join.

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 4

SQL 2000 does not allow the syntax:

select A.columns, B.columns
from TableA as A
join dbo.MyFunction(TableA.ID) as Bon ...

However, you will be able to get this in SQL 2005 with the APPLY operator:

select A.columsn, B.columns
from TableA
CROSS APPLY dbo.MyFunction(TableA.ID)

This does exactly what you want: applies the table function to the inputs from TableA. Note that this is similar to a cross join. If the function returns multiple rows, then columns from A will be repeated for each row from B.

But as I said. we'll have to wait til next year for this functionality....

Scott Thornburg

Volt Information Sciences