problem passing UDF scalar result to UDF table function

[roger]

I'm having difficulties invoking a user defined table function,
when passing to it a parameter that is the result of another
user defined function.

My functions are defined like so:

drop function dbo.scalar_func
go
create function dbo.scalar_func()
returns int
begin
return 1
end
go

drop function dbo.table_func
go
create function dbo.table_func(@p int)
returns table
return (select @p as id )
go
Given the above, I can do the following:

Select from the scalar function works:
1> select dbo.scalar_func() as scalar_result
2> go
scalar_result
-------------
1

Selecting from the table function works, if i pass a
constant value (or a variable)

1> select id from dbo.table_func(1)
2> go
id
-------------
1

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly

[-P-]

"roger" <ro****@softix.com> wrote in message news:f6**************************@posting.google.c om...

<snip>

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

Thanks kindly

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

--
Paul Horan
VCI Springfield, MA

[Erland Sommarskog]

roger (ro****@softix.com) writes:

But, if I try to pass the table function the return value
of the scalar function in one call, it doesn't work,
producing the following error:

1> select id from dbo.table_func( dbo.scalar_func() )
2> go
Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

What am I missing here?

I don't think you can pass expressions as parameters to table-valued
functions; you can only pass constants and variables.

However, I was looking around in Books Online, but I could not find
a passage which actually says so.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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

[roger]

"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI********************@adelphia.com>...

"roger" <ro****@softix.com> wrote in message news:f6**************************@posting.google.c om...

<snip>

It's barking at the period in the nested call. Try running it without qualifying the function name:

1> select id from dbo.table_func( scalar_func() )

No, that isn't it.

You have to qualify a scalar function with the owner name.
Curiously, you don't have to qualify a table valued function
in this way.

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.
I can't see any particular rhyme or reason to this,
it's just the way it seems to be.

[Erland Sommarskog]

roger (ro****@softix.com) writes:

So, this works
select * from table_function()
but this does not
select scalar_function() from table
requiring instead
select dbo.scalar_function() from table.
I can't see any particular rhyme or reason to this,
it's just the way it seems to be.

The reason is that with out the reqiurement of a two-part name there
would be no possibility to distinguish between scalar system functions
and scalar UDF. And if they look the same syntactically, they share the
same name space, which would mean that each time MS added a new system
function, they would risk to break existing code.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

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