Passing Temp Table Values into a UDF

This one is interesting...

Is there any way to pass a joined parameter into a UDF as I'm attempting below?

I have a temp table that I'm trying to create:

create table #t3
(bmkPerson int primary key,
LangCode nchar(5),
SName varchar(1000)
)
insert into #t3
select t2.bmkPerson, t2.LangCode,
select SName from SName_trans_udf(t2.LangCode)
from #t2 t2

Thanks in advance,

Renae
hilary321@.yahoo.comRenae (hilary321@.yahoo.com) writes:
> Is there any way to pass a joined parameter into a UDF as I'm attempting
> below?
> I have a temp table that I'm trying to create:
> create table #t3
> ( bmkPerson int primary key,
> LangCode nchar(5),
> SName varchar(1000)
> )
> insert into #t3
> select t2.bmkPerson, t2.LangCode,
> select SName from SName_trans_udf(t2.LangCode)
> from #t2 t2

No, you cannot pass a column value to a table-valued UDF. And when you
think of it it is logical: what result would you get? For each column
you get a whole table back. A scalar UDF works fine, because it's a
scalar value you need here.

SQL 2005 however does permit you to pass a column to a table-valued
UDF, but there is a special syntax for this. (Which I have not explored
yet, so I cannot give any examples.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp