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"roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.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|||roger (rogerr@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"-P-" <ent_must_dieAThotmailDOTcom> wrote in message news:<UI2dnT_yvLdgPQndRVn-hQ@.adelphia.com>...
> "roger" <rogerr@.softix.com> wrote in message news:f6e08af1.0405012257.4c2472cf@.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.|||roger (rogerr@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.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.
Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg
select * from table(my_user_function(args))
I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.
Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.
As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.
Still, I'm thankful that it is there at all I guess.|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94DEEA82AECD9Yazorman@.127.0.0.1>...
> roger (rogerr@.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.
Oracle PL/SQL uses the keyword TABLE to introduce a function
that returns a table. eg
select * from table(my_user_function(args))
I would have thought that the context in which the function
is being called would tell you whether a scalar or table result
is required, but maybe there are more scenarios than I can imagine
off hand.
Using the qualified name to distinguish the function return type,
if that is really what that is about, is truly horrid.
As is not being able to pass the result of another function as
an argument. Kind of smells like this stuff was added in a hurry.
Still, I'm thankful that it is there at all I guess.|||roger (rogerr@.softix.com) writes:
> Oracle PL/SQL uses the keyword TABLE to introduce a function
> that returns a table. eg
> select * from table(my_user_function(args))
> I would have thought that the context in which the function
> is being called would tell you whether a scalar or table result
> is required, but maybe there are more scenarios than I can imagine
> off hand.
The issue is about distinguishing scalar functions from table functions.
The issue is about distinguishing different sorts of scalar functions,
system functions vs. user-defined ones. SQL Server have no problem
to tell whether you are using a table-valued or scalar function. Or
more correctly: there are no places in the grammar where you can use both.
The system functions had already invaded the flat name space for
SELECT fun(x)
Of course, there are a couple of more options MS could have chosen. For
instance decide that all future scalar system functions would have a
prefix.
This is actually what they have done for table-valued functions:
SELECT * FROM ::fn_helpcollations()
This was possible, since there was no table-valued system functions
prior to SQL2000.
> As is not being able to pass the result of another function as
> an argument. Kind of smells like this stuff was added in a hurry.
It is quite consistent with how you call stored procedures. You
cannot pass expressions in calls to stored procedures with the
EXEC statement. And in fact, you can invoke scalar functions with
the EXEC statement too.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql
No comments:
Post a Comment