Inline function in subquery not working

  • I have a user-defined function called ArticlesByProject(@ProjectID) that returns a table. I can get the number of Articles for a given Project like this:

    Select ArticleCount=Count(*) from ArticlesByProject(41)

    So far so good. What I'd like to do now is have a view that includes a column with the number of articles for each project. So I've tried this:

    Select

    ArticleCount = (Select count(*) from dbo.ArticlesByProject(p.ProjectID))

    from v_Projects p

    But I get this error:

    Line 5: Incorrect syntax near '.'.

    The period in question is the period in p.ProjectID - if I replace p.ProjectID with a number, it works fine. This executes but is obviously not very useful:

    Select

    ArticleCount = (Select count(*) from dbo.ArticlesByProject(41))

    from v_Projects p

    Any ideas? Does T-SQL not allow a reference to the columns in the outer query from within a subquery containing a user-defined function?

    Thanks

    Herb

  • Herb,

    I ran into the same limitation that you are.  UDF's that return a table are only executed once. 

    From Books On Line

    You can invoke a user-defined function that returns a table where table expressions are allowed in the FROM clause of SELECT, INSERT, UPDATE, or DELETE statements. An invocation of a user-defined function that returns a table can be followed by an optional table alias. The following example illustrates calling the table-valued function ufnGetContactInformation in the FROM clause of a SELECT statement.

    When a user-defined function that returns a table is invoked in the FROM clause of a subquery, the function arguments cannot reference any columns from the outer query.

    Static, read-only cursors are the only type of cursor that can be opened on a SELECT statement whose FROM clause refers to a user-defined function that returns a table.

    A SELECT statement that references a user-defined function that returns a table invokes the function one time.

    Maybe the place to put your count(*) is in the view itself.....

    Mike

  • You are trying to create "3 dimentional" query: assign table to each cell in another table. SQL Server cannot handle this.

    Create scalar function for

    Select count(*) from dbo.ArticlesByProject(@ProjectID)

    and use in your query.

    _____________
    Code for TallyGenerator

  • That's a good solution. Thanks for your help.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply