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.CountOfID, 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.partID;
Forum is the table
CountofUsers is the stored procdure
8  2105 
...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. - CREATE FUNCTION dbo.CountofUsers
-
RETURNS TABLE
-
AS RETURN
-
SELECT Count(forum.ID) AS CountOfID, forum.partID
-
FROM forum
-
GROUP BY forum.partID
-
HAVING (((forum.partID)<>0));
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.PartID) INNER JOIN NumberPosts ON forum.author = NumberPosts.author;
this is the line i need to use it with
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.CountofUsers ()
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.PartID) INNER JOIN NumberPosts ON forum.author = NumberPosts.author
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_spec) AS
BEGIN
(FUNCTION BODY)
END
Can You Help Please!
Can anyone help??? Please
CREATE FUNCTION dbo.CountofUsers()
RETURNS TABLE
AS RETURN
SELECT Count(*) AS CountOfID, forum.partID
FROM forum
WHERE forum.partID<>0
GROUP BY forum.partID
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.CountOfID,forum.subject, forum.author, forum.message, forum.responder, forum.dtdate, forum.ipaddress, forum.PicID
FROM (forum LEFT JOIN Countofusers() as countOfUsers ON forum.ID = Countofusers.PartID)
i get "Invalid object name 'forum'.
forum is a table that exists
Can you Help?? (yet again!)
...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.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: dinesh prasad |
last post by:
I'm trying to use a servlet to process a form, then send that data to
an SQL server stored procedure. I'm using the WebLogic 8 App. server.
I am able to retrieve database information, so I know my...
|
by: Együd Csaba |
last post by:
Hi,
I've a problem with some of my stored procs. My config is: RH7.1, Postgres
7.3.2
I have converted a few fields of a few tables from one type to another.
After this I made all the necessary...
|
by: Rhino |
last post by:
I've spent the last couple of hours trying to figure out how to debug a Java
stored procedure and am just going in circles. The last straw came when I
got "Cannot open input stream for default"...
|
by: Rhino |
last post by:
This question relates to DB2 Version 6 on OS/390.
Can a (COBOL) stored procedure on this platform do file I/O, i.e. write to a
sequential file?
I am trying to debug a stored procedure. As far...
|
by: Tim Marshall |
last post by:
I was following the thread "Re: Access Treeview - Is it Safe Yet?" with
interest and on reading the post describing Lauren Quantrell's
SmartTree, I've run into something I don't understand: Stored...
|
by: Dino L. |
last post by:
How can I run stored procedure (MSSQL) ?
|
by: jed |
last post by:
I have created this example in sqlexpress
ALTER PROCEDURE .
@annualtax FLOAT
AS
BEGIN
SELECT begin1,end1,deductedamount,pecentageextra
FROM tax
|
by: Milkstr |
last post by:
is it possible in MS SQL to write a stored procdure that uses the FROM statment to get imformation from an existing stored procdure, at the moment i get invaild object as it seem to only like the...
|
by: mirandacascade |
last post by:
Questions toward the bottom of the post.
Situation is this:
1) Access 97
2) SQL Server 2000
3) The Access app:
a) sets up pass-thru query
b) .SQL property of querydef is a string, the...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
