t-sql COUNT on user defined function returned value

Question

I have a CTE as below (logic removed)

;with cte_a as
(
    select ID, Count(AnotherID) as Counter
    from Table_A
    group by ID
)

and a user defined function that takes an Id as input and returns a table.

udf_GetRelatedItemIds(@Id)

I wanted to just count the number of related item ids returned from the user defined function for each ID in cte_a.

I was trying something like below but it didn't work

;with cte_a as
(
    select ID, Count(AnotherID) as Counter
    from Table_A
    group by ID
)
select 
ID, 
Count(select RelatedId from udf_GetRelatedItemIds(ID))
from cte_a

Please suggest a solution.

Tahbaza · Accepted Answer · 2010-07-08 18:30:33Z

2

What about

with cte_a as
(
    select ID, Count(AnotherID) as Counter
    from Table_A
    group by ID
)
select 
a.ID, 
(SELECT COUNT(*) FROM udf_GetRelatedItemIds(a.ID)) as IntersectCount
from cte_a a

answered Jul 8, 2010 at 18:30

Tahbaza

9,5542 gold badges28 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tom H · Accepted Answer · 2010-07-08 18:35:02Z

0

SELECT
    T.ID,
    (SELECT COUNT(RelatedId) FROM udf_GetRelatedItemIds(T.ID)) AS cnt
FROM
    Table_A T
GROUP BY
    T.ID

WARNING!!! The performance here is likely to be horrendous as it needs to run that function once for every row in Table_A. If you can recreate your function logic in a view that gives you results for all of the IDs you'd probably be better off.

answered Jul 8, 2010 at 18:35

Tom H

47.6k15 gold badges90 silver badges134 bronze badges

Collectives™ on Stack Overflow

t-sql COUNT on user defined function returned value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related