COUNTing Rows Returned From A User Defined Function

[daveys110]

Hi! I'm trying to return the number of rows returned by a UDF which returns a table:

The UDF returns a TABLE defined with only 1 column (storeID INT)

I want to get the number of rows returned by the UDF:

StoreIDListForGroup( <groupID> )

Example usage that works:

SELECT sg.id, sg.title, (SELECT COUNT(storeID) FROM dbo.StoreIDListForGroup(1)) AS storeCount
FROM StoreGroups sg
ORDER BY sg.title

This will give me the store group id, title and the count of number of stores in group 1.

I need it to count the number of rows per group.

Example which doesn't work:

SELECT sg.id, sg.title, (SELECT COUNT(storeID) FROM dbo.StoreIDListForGroup(sg.id)) AS storeCount
FROM StoreGroups sg
ORDER BY sg.title

What's the correct syntax for this?

Thanks,
David

 

[JarlH]

Which DBMS are you using? (According to the ANSI/ISO SQL standard you can't call UDF's the way you do...)

 

[daveys110]

MS SQL 2000

 

[JarlH]

I haven't got a clue how to call UDF's in MS SQL... If you want a quick answer I suggest you post your question in an MS SQL forum instead.

 

[daveys110]

Thanks,

I've solved this now - I just made another function to COUNT the rows returned by the first function.

EG:

SELECT sg.id, sg.title, dbo.CountStoresInGroup(sg.id) AS storeCount
FROM StoreGroups sg
ORDER BY sg.title

Thanks,
Dave