SQL Server - Function Table Call

microsoft.public.sqlserver.programming

18 Aug 2006 1:55 AM

Amb

I have a request from a friend that I personally don't think is possible
without a cursor loop:

Lets say I have a function that returns a table: Similar to below - and it
will only ever, no matter what, return one row.

create function dbo.fn_henfruit(@a int, @b int) returns table
as
return select 'Egg' as Egg1, @a as OrigA, @b as OrigB

Now lets say I have a table filled with A and B values, picked at random...

create table tblchickens(a int, b int)
insert into tblchickens values(1,1)
insert into tblchickens values(1,2)
insert into tblchickens values(4,3)
insert into tblchickens values(5,5)

Now can I select the values from that table, and the values of egg1 from the
function against each row?

eg: SELECT chk.a, chk.b, (SELECT TOP 1 Egg1 FROM dbo.fn_henfruit(a,b)) AS
funcValue
FROM tblchickens chk

But this gives an error "'a' is not a recognized OPTIMIZER LOCK HINTS
option.". If I specify the chk alias in front of a (chk.a, chk.b) then I get
"Incorrect syntax near ." (This happens regardless of the alias, or lack
thereof)

In fact, changing it to a join fails as well. But if I change the A and B
to arbitrarily picked numbers, it then works, but not with the values from
the database.

More interestingly is if I use variables:

declare @a_a int
declare @a_b int
declare @resultEgg Varchar(3)
select
   @a_a = ch.a
   ,@a_b = ch.b
   ,@resultEgg = (select egg1 from dbo.fn_henfruit(@a_a,@a_b))
from
   tblchickens ch

The function returns with "The command(s) completed successfully." and is
the only variation I have to date that actually executes. Of course reading
multiple value selects into variables is about the equivalent of doing a
"select top 1 " and then wondering why the query never returns 2 or more
rows. So the only working variation so far is of no practical use.

I dont believe this can be done without the use of cursors.   If my friend
takes my advice he will change his function to a view! But if it could be
done, it would be interesting indeed

Amb

18 Aug 2006 4:22 AM

Tom Cooper

Maybe someone who knows more than I do will jump in and correct me and we
will both learn something. But I believe you cannot do this.

Table valued functions can only be called in the FROM clause. Furthermore,
if they are used in a subquery, they cannot reference any column in the
outer query. Finally, SELECT statements that reference a table valued
function only invoke that function once which seems to preclude what you
want to do since you want to invoke it once per row in tblchickens.

Relavent info from SQL 2000 BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_11_78md.asp

And from SQL 2005 BOL (scroll down to the "Invoking User-defined Functions
That Return a table Data Type" topic):
http://msdn2.microsoft.com/en-us/library/ms175562.aspx

Tom

Show quote

"Amb" <A**@discussions.microsoft.com> wrote in message
news:A5C78826-3B83-4BAD-884B-3CF7B5B3BE92@microsoft.com...
>I have a request from a friend that I personally don't think is possible
> without a cursor loop:
>
> Lets say I have a function that returns a table: Similar to below - and
> it
> will only ever, no matter what, return one row.
>
> create function dbo.fn_henfruit(@a int, @b int) returns table
> as
> return select 'Egg' as Egg1, @a as OrigA, @b as OrigB
>
> Now lets say I have a table filled with A and B values, picked at
> random...
>
> create table tblchickens(a int, b int)
> insert into tblchickens values(1,1)
> insert into tblchickens values(1,2)
> insert into tblchickens values(4,3)
> insert into tblchickens values(5,5)
>
> Now can I select the values from that table, and the values of egg1 from
> the
> function against each row?
>
> eg: SELECT chk.a, chk.b, (SELECT TOP 1 Egg1 FROM dbo.fn_henfruit(a,b)) AS
> funcValue
> FROM tblchickens chk
>
> But this gives an error "'a' is not a recognized OPTIMIZER LOCK HINTS
> option.". If I specify the chk alias in front of a (chk.a, chk.b) then I
> get
> "Incorrect syntax near ." (This happens regardless of the alias, or lack
> thereof)
>
> In fact, changing it to a join fails as well. But if I change the A and B
> to arbitrarily picked numbers, it then works, but not with the values from
> the database.
>
> More interestingly is if I use variables:
>
> declare @a_a int
> declare @a_b int
> declare @resultEgg Varchar(3)
> select
>   @a_a = ch.a
>   ,@a_b = ch.b
>   ,@resultEgg = (select egg1 from dbo.fn_henfruit(@a_a,@a_b))
> from
>   tblchickens ch
>
> The function returns with "The command(s) completed successfully." and is
> the only variation I have to date that actually executes. Of course
> reading
> multiple value selects into variables is about the equivalent of doing a
> "select top 1 " and then wondering why the query never returns 2 or more
> rows. So the only working variation so far is of no practical use.
>
> I dont believe this can be done without the use of cursors.   If my friend
> takes my advice he will change his function to a view! But if it could be
> done, it would be interesting indeed
>
> Amb

18 Aug 2006 5:17 AM

Steve Kass

Amb,

In SQL Server 2005, the usage of table-valued functions has
been loosened, and your query will succeed. (Note, however,
that TOP 1 without ORDER BY, as you have in the subquery,
is nondeterministic - you might get any row at all of the table-
valued function). To use a table valued function in the main
FROM clause, you can still do it in 2005 with the APPLY
operator.

In SQL Server 2000, you can create a scalar function to
return what you need (calling the table-valued function)
and use the scalar function in the query. This will likely be
very inefficient, and you should look for other ways to
solve the problem at hand, I'd say.

create table tblchickens(a int, b int)
insert into tblchickens values(1,1)
insert into tblchickens values(1,2)
insert into tblchickens values(4,3)
insert into tblchickens values(5,5)
go

create function fn_henfruit(
@a int,
@b int
) returns table as return
select @a as Egg1 union all select @b
go

create function fn_henfruit_scalar (
@a int,
@b int
) returns int as begin
return (
select top 1 Egg1 from fn_henfruit(@a,@b)
)
end
go

SELECT chk.a, chk.b, dbo.fn_henfruit_scalar(a,b) AS
funcValue
FROM tblchickens chk
go

drop table tblchickens
drop function fn_henfruit
drop function fn_henfruit_scalar

Steve Kass
Drew University
www.stevekass.com

Show quote

"Amb" <A**@discussions.microsoft.com> wrote in message news:A5C78826-3B83-4BAD-884B-3CF7B5B3BE92@microsoft.com...
>I have a request from a friend that I personally don't think is possible
> without a cursor loop:
>
> Lets say I have a function that returns a table: Similar to below - and it
> will only ever, no matter what, return one row.
>
> create function dbo.fn_henfruit(@a int, @b int) returns table
> as
> return select 'Egg' as Egg1, @a as OrigA, @b as OrigB
>
> Now lets say I have a table filled with A and B values, picked at random...
>
> create table tblchickens(a int, b int)
> insert into tblchickens values(1,1)
> insert into tblchickens values(1,2)
> insert into tblchickens values(4,3)
> insert into tblchickens values(5,5)
>
> Now can I select the values from that table, and the values of egg1 from the
> function against each row?
>
> eg: SELECT chk.a, chk.b, (SELECT TOP 1 Egg1 FROM dbo.fn_henfruit(a,b)) AS
> funcValue
> FROM tblchickens chk
>
> But this gives an error "'a' is not a recognized OPTIMIZER LOCK HINTS
> option.". If I specify the chk alias in front of a (chk.a, chk.b) then I get
> "Incorrect syntax near ." (This happens regardless of the alias, or lack
> thereof)
>
> In fact, changing it to a join fails as well. But if I change the A and B
> to arbitrarily picked numbers, it then works, but not with the values from
> the database.
>
> More interestingly is if I use variables:
>
> declare @a_a int
> declare @a_b int
> declare @resultEgg Varchar(3)
> select
>   @a_a = ch.a
>   ,@a_b = ch.b
>   ,@resultEgg = (select egg1 from dbo.fn_henfruit(@a_a,@a_b))
> from
>   tblchickens ch
>
> The function returns with "The command(s) completed successfully." and is
> the only variation I have to date that actually executes. Of course reading
> multiple value selects into variables is about the equivalent of doing a
> "select top 1 " and then wondering why the query never returns 2 or more
> rows. So the only working variation so far is of no practical use.
>
> I dont believe this can be done without the use of cursors.   If my friend
> takes my advice he will change his function to a view! But if it could be
> done, it would be interesting indeed
>
> Amb