Calling Function From Stored Procdure ???

[Milkstr]

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER...."
Please can anyone help!!

[dsithoo]

In order to use table functions, assuming you are using SQL 2005 and not SQL 2000 in which case you are stuck and would have to either use cursors or else create an empty column which you update with the count afterwards, you need to join using either CROSS APPLY (like an inner join) or OUTER APPLY (like a left outer join).

E.g.

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers() CountofUsers
WHERE forum.partID=0
AND CountofUsers.partID = forum.partID
ORDER BY dtDate DESC

I would probably re-write the function to take a parameter for partID for optimal efficiency:

CREATE FUNCTION dbo.CountofUsers() (@partID int)
RETURNS @CountOfUsers TABLE (
CountOfID int,
partID int
)
AS
BEGIN
INSERT INTO @CountOfUsers (
CountOfID
)
SELECT Count(*) AS CountOfID
FROM forum
WHERE forum.partID=(@partID
RETURN
END

and then use the select like this:

SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID,
CountofUsers.CountOfID
FROM forum
OUTER APPLY dbo.CountofUsers(forum.partID) CountofUsers
WHERE forum.partID=0
ORDER BY dtDate DESC

Hope that helps.

Alternatively, I have found a way to be able to pass table results from one SQL stored procedure up to a caller via XML datatype output parameters. This simulates MARS behaviour with ADO.NET, allowing more than one resultset to be passed up as well. Check Books Online for more info.

I'm a total novice with SQL, so please bare with me.
I'm trying to write a simple forum for my web site, i've done it before using Access but struggling to use the same idea with SQL. In acces i had one table for all the post and replies, i then had several querys that filtered off just the main topics and the number of replies for each thread. I did this by using multiple queries joined together, i've now found out that in SQL you cant call one strored procdure from another, so you have to use functions instead.

I have a stored procdure that filters off just the starter threads, but i want to also show how many replies ther has been for each topic and put then in date order of when they were posted.

this is my main stored procdure

CREATE PROCEDURE forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, forum.subject, forum.author, forum.message, forum.responder, forum.dtDate, forum.IpAddress, forum.PicID
FROM forum
WHERE forum.partID=0
ORDER BY dtDate DESC

this works ok and shows the main threads, but i also want to list the number of posts for each thread, i've got a function

CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID

this should count the number of posts for each thread, but how do i combined the 2 together

i've messed about with adding "countofusers()" to the FROM clause in the procdure but i'm struggling!!! In acesses it something like "JOIN INNER...."
Please can anyone help!!

[Milkstr]

Thanks for you explaination, very usefull, i am indeed using SQL 2000, i i guest i'm a little stuck. I thought what i was doing was quite straight forward but it apppears not to be!
I've tried using a sub query but didn't have much joy.
Can you suggest anything else that will get me going?? i'm only wrting a simple forum. (i dont have to use the table function, this was only a suggestion to me, if there is a better way for the fuction to work i can try it)

[dsithoo]

Probably the easiest method is to create a temp table and insert the results of the SELECT statement from your function into it (either directly as a select or from the function or a stored procedure, if you want the piece of code to be reusable). Then perform a join to your previous result on partID to get the count value.