Function that returns a table

I have a function that returns a single row table with two columns:

dbo.Fun1(@.param1) : colA and colB

I tried to create a stored procedure that use this function:

select col1, col2, dbo.Fun1(col1) from table1

The result is : Invalid object name 'dbo.Fun1'

There is no join between table1 and Fun1, how can I select the both columns of Fun1 ?

Thanks in advance.

Long

Can u paste the declaration of the function?|||You are trying to use a table-valued UDF like a scalar UDF which is incorrect. You can use a table-valued function only in the FROM clause or as a table source. In any case, what you are trying to do is not possible in SQL Server 2000 since you can only pass variables or constants as parameters to table-valued UDFs. In SQL Server 2005, you can use the APPLY operator to the same.|||

Thanks, Umachandar,

I have to do the selection like this:

select col1, col2, (select colA from dbo.Fun1(col1) ), ( select colB from dbo.Fun1(col1))

from table1

It works, but I'm not satisfied, as it calculates the function twice.

Any other ideas?

Thanks in advance.

Long

|||

Hi,
due to the fact that you have to execute the statement once per row, there is no way to do it ohter than your mentioned way.

Without knowing your Function I would assume that even this is very wacky, because your Return could return more than one value ?! So you have to make sure from your query / ir function that only one row will be returned.

HTH, Jens Suessmeyer.

|||

I don't see how this will work in SQL2000. If you are on SQL2005 then you can simplify the query by using APPLY operator like:

select t.col1, t.col2, f.colA, f.colB

from table1 as t

cross apply dbo.Fun1(t.col1) as f