Table-valued user function?

[Jason 'Bug' Fenter [TeamSybase]]

I have a table-valued function in MS SQL Server 2000. How do I implement
that in SQLAnywhere 10?


The underlying data is a hierarchy of areas with an "id" column and a
"parentid" column. The user function gives all children of a given ID,
regardless of the nesting level. So given that "Area A" has child "Area
B" which itself has child "Area C", fGetAreas("Area A") would return
both "Area B" and "Area C". And in MSSQL, the results look like a table.
As in:
SELECT SubArea
FROM fGetAreas("Area A")


How do I do something like that in SQLAnywhere 10?

[Volker Barth]

Jason,

I don't think that table-valued functions are available in SA. However,
starting with ASA 9 you can use procedures in the FROM clause, so an
alternative would be something like:

create procedure pGetAreas(in strArea varchar)
result (SubArea varchar, ...)
...;

select SubArea
from pGetAreas("Area A");


AFAIK MS SQL can't handle procedure results that way but can only store
procedure results to tables with the EXEC INSERT statement.

HTH
Volker

Post by Jason 'Bug' Fenter [TeamSybase]
I have a table-valued function in MS SQL Server 2000. How do I implement
that in SQLAnywhere 10?
The underlying data is a hierarchy of areas with an "id" column and a
"parentid" column. The user function gives all children of a given ID,
regardless of the nesting level. So given that "Area A" has child "Area
B" which itself has child "Area C", fGetAreas("Area A") would return
both "Area B" and "Area C". And in MSSQL, the results look like a table.
SELECT SubArea
FROM fGetAreas("Area A")
How do I do something like that in SQLAnywhere 10?