By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,333 Members | 1,850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,333 IT Pros & Developers. It's quick & easy.

problem passing UDF scalar result to UDF table function

P: n/a
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
Jul 20 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
-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
Jul 20 '05 #2

P: n/a
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
Jul 20 '05 #3

P: n/a
"-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.
Jul 20 '05 #4

P: n/a
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
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.