January 31, 2006 at 2:34 pm
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
January 31, 2006 at 6:41 pm
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
January 31, 2006 at 8:33 pm
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
February 1, 2006 at 7:59 am
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