SQL Server Performance

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

Discussion in 'SQL Server 2008 General DBA Questions' started by AMOURGH, Feb 26, 2009.

  1. AMOURGH Member

    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 db:confused:rgTreeAssociation 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
  2. Adriaan New Member

    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.
  3. AMOURGH Member

    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
  4. AMOURGH Member

    Oops
    it needs a ; after insert.
  5. Adriaan New Member

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

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

    SELECT * FROM <table-valued function>
  8. FrankKalis Moderator

    Btw, what you had in your example post later on was a Common Table Expression which is different to a Table-Valued Function.
  9. Adriaan New Member

    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.
  10. AMOURGH Member

    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
  11. AMOURGH Member

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

    Or an example.
    i dont wonna use Cursors
  13. Adriaan New Member

    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.)
  14. AMOURGH Member

    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.
  15. Adriaan New Member

    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.
  16. AMOURGH Member

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

    I assume the table-valued function is gone?

Share This Page