Using an Table-value-function a part of select query

Hello everybody,
I try to call a udf returnin a table as a part of a query but it seems SQL Server doent accept .
I used group by but it doesnt work neither !!
UDF=dbo.GetBusinessGeoChildren_Function(BusinessGeoTable.BusinessGeoId)
SELECT serviceManagement.id,BusinessGeoTable.BusinessGeoId,site.Name+'/'+businessTree.NameFROM ServiceManagement serviceManagement ,@BusinessGeoTable BusinessGeoTable
INNER JOIN dbo.GetBusinessGeoChildren_Function(BusinessGeoTable.BusinessGeoId) AS businessGeoChildrenTable ON serviceManagement.OrgTreeAssociations_Id=businessGeoChildrenTable.BusinessGeoIdINNER JOIN @TelecomSubCatTable telecomSubCatTable ON serviceManagement.TelecomSubCategory_Id=telecomSubCatTable.TelecomSuCatbId
INNER JOIN dbrgTreeAssociation orgTreeAssociation ON BusinessGeoTable.BusinessGeoId=orgTreeAssociation.IdINNER JOIN Site site on site.Id=orgTreeAssociation.Site_Id
INNER JOIN BusinessTree businessTree ON businessTree.Id=orgTreeAssociation.BusinessTree_IdWHERE DATEDIFF(day,serviceManagement.StartDate,@EndDate)>0 AND DATEDIFF(day,@StartDate,serviceManagement.EndDate)>0
GROUP BY BusinessGeoTable.BusinessGeoId
Any idea welcome .
Thanks,
Rachid

From BOL > CREATE FUNCTION (Transact-SQL):
Only constants and @local_variables can be passed to table-valued functions.
That rules out using a column as a parameter ...
You might find the WITH syntax useful, or a derived table.
You might even find a way to re-incorporate the function into the main body of your query.

Hi,
I tried to use with .i ve begun with a simple example
But it doesnt even compile well:
ECLARE
@EmployeeTable TABLE(EmployeeId
int,ManagerId
int
)INSERT INTO @EmployeeTable
SELECT 1,NULL
UNION ALL
SELECT 2,NULL
UNION ALLSELECT 3,2
UNION ALLSELECT 4,1
UNION ALLSELECT 5,2
WITH DirReps(ManagerId,DirectReports) AS(SELECT ManagerId,COUNT(*) AS DirectReports FROM @EmployeeTable
WHERE ManagerID IS NULL
GROUP BY ManagerId)SELECT
ManagerId,DirectReports FROM
DirReps ORDER
BY ManagerIDGO

Oops
it needs a ; after insert.

It also needs a D before ECLARE - anyway ... problem solved with the semi-colon?

Hi ,
Could someone please show me an example of Table-value-function used as a part of select query?
Thanks

SELECT * FROM dbo.myFunction(parameter_value)
The parameter value can only be a specific value that you write out (like 'abc' or 3412) or a scalar variable (like @myvarcharvariable). --- Probably also a scalar function (like GetDate()).
With a scalar function (one that does not return a table) the parameter can also be a reference to a column.

HI
FrankKalis ,my pb is in my first post.
i want to call a table-value function for elements that are in other table.
Parameter=Table column

But as Adriaan has said the parameter cannot be a variable,shud be specific value.
Adriaan do u have another alternative?

Or an example.
i dont wonna use Cursors

If the core of the udf is simply a SELECT statement (however complex) then use that same body as a derived table. Assuming that the parameter for udf acts as a filter, you should be able to JOIN the main table to the derived table.
If the core of the udf is more complex, then consider filling a table variable or a temp table, and use that in your query. (In the end, that is what SQL Server is doing in the background for you with a table-valued function.)

How can i fill a temporary table with the udf using a colum as parameter.
UDF =GetBusinessGeoChildren_Function(BusinessGeoId) that gets children in a table for a given BusinessGeoId.
how can i get the children of a set of BusinessGeoId.

You will use the core of the UDF to gather the data and insert it into the temp table. You will involve in this process the same table with the "parameter column", primarily to limit the data that will be inserted into the temp table, but also to make sure that the BusinessGeoId is copied into the temp table. Now you can join that same table once again to the temp table in the main query.

Thank u Adriaan.
I have found a solution to avoid cursors.

I assume the table-valued function is gone?