Stored Procdures MS SQL

[Milkstr]

Can anyone help, i'm just starting out with MS SQL on a new web page and i'm having problems writing a stored procdure, its for a simple forum on a web page.
I have a table with the main forum information, i then have a stored procdure

"SELECT Count(forum.ID) AS CountOfID, forum.partID
FROM forum
GROUP BY forum.partID
HAVING (((forum.partID )<>0));"

this is to work out which is the main posts and not the replies, i then have the main table with all the records.

i then am trying to write another stored procdure that looks at the main forum table and combines that with the stored procdure, however i can seem to get the last stored procdure to work, can a stored prcdure use a SELECT statment for a stored procdure and a table, or do i need to prefix the stored procedure with some thing??

SELECT forum.ForumID, forum.ID, forum.partID, CountofUsers.Co untOfID, forum.subject, forum.author, forum.message, forum.responder , forum.dtDate, forum.email, forum.forumIP, forum.PicID
FROM forum LEFT JOIN CountofUsers ON forum.ID = CountofUsers.pa rtID;

Forum is the table
CountofUsers is the stored procdure

[almaz]

...Forum is the table
CountofUsers is the stored procdure

You cannot use stored procedures in the FROM clause. But if you'll rewrite your stored procedure as FUNCTION, then you'll be able to use it in the FROM clause.
[CODE=sql]CREATE FUNCTION dbo.CountofUser s
RETURNS TABLE
AS RETURN
SELECT Count(forum.ID) AS CountOfID, forum.partID
FROM forum
GROUP BY forum.partID
HAVING (((forum.partID )<>0));[/CODE]

[Milkstr]

thanks for that, i'm getting an error, it just says
Incorrect syntax near 'RETURNS'.

do i then write a function call in my store procdure to call this???

FROM (forum LEFT JOIN Countofusers ON forum.ID = Countofusers.Pa rtID) INNER JOIN NumberPosts ON forum.author = NumberPosts.aut hor;

this is the line i need to use it with

[almaz]

thanks for that, i'm getting an error, it just says
Incorrect syntax near 'RETURNS'.

Ooops, forgot to add parentheses. Should be:

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

Usage:

FROM (forum LEFT JOIN Countofusers() as CountOfUsers ON forum.ID = CountOfUsers.Pa rtID) INNER JOIN NumberPosts ON forum.author = NumberPosts.aut hor

[Milkstr]

Sorry to be a pain, but i'm still getting an error

Error -2147217900
Incorrect syntax near the keyword 'WHERE'.

I'm using 1and1 as my provider and its MS SQL 2000 server, when i go to the user defined functions is prompts me with this

CREATE FUNCTION [Function Name]
RETURNS (return_type_sp ec) AS
BEGIN
(FUNCTION BODY)
END

Can You Help Please!

[Milkstr]

Can anyone help??? Please

[almaz]

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

[Milkstr]

Thanks, the functions works, but still struggling to get the stored procdure to work

CREATE PROCEDURE Forum2 AS
SELECT forum.ForumID, forum.ID, forum.partID, Countofusers.Co untOfID,forum.s ubject, forum.author, forum.message, forum.responder , forum.dtdate, forum.ipaddress , forum.PicID
FROM (forum LEFT JOIN Countofusers() as countOfUsers ON forum.ID = Countofusers.Pa rtID)



i get "Invalid object name 'forum'.

forum is a table that exists

Can you Help?? (yet again!)

[almaz]

...i get "Invalid object name 'forum'.

forum is a table that exists

Can you Help?? (yet again!)

Please describe what actions did you take to find out the problem, because it looks like as soon as you encounter an error you post it here instead trying to understand it. Previous one was just because of incorrect order of clauses in the SELECT statement, and it can be easily figured out from Books Online.

Concerning your current problem: there is nothing wrong with your query (except if you have case-sensitive collation), you may just have changed the active database context to another database. Please post the DDL of the [forum] table, if you'll have more questions.