stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

stored procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2003-09-25 : 08:11:33

Dan writes "Hi,

I am trying to convert a mdb database to a sql database by import it into sql sever. But the sql sever can only convert the table in mdb database but not the stored procedures in it.

Could u please convert the follwing stored procedures from my mdb database to a store procedure in sql server?

It is a database for a message board. Each board has serveral topics and under each topic, there are several messages. The tables and database of this database are as following:

Table: Board

Board_ID

Name
description

Table: Topic

Topic_ID
Board_ID
Title
Description
created_by
Created_date

Table: message

Message_ID
Topic_ID
UID
Body
Created_Date

This stored proc shows the count of message under each board and the date of the last messeage of each board:

PARAMETERS bid Long;
SELECT Count(message.CREATED_DATE) AS countofdate, Max(message.CREATED_DATE) AS maxofdate
FROM (board INNER JOIN topic ON board.BOARD_ID = topic.BOARD_ID) INNER JOIN message ON topic.TOPIC_ID = message.TOPIC_ID
GROUP BY board.BOARD_ID
HAVING (((board.BOARD_ID)=[bid]));

Theis stored proc shows the count of the message, topic_id and topic_title of the message Under each TOPIC:

PARAMETERS top_id Long;
SELECT Count(MESSAGE.MESSAGE_ID) AS MSG_COUNT, TOPIC.TOPIC_ID, TOPIC.TITLE
FROM TOPIC INNER JOIN MESSAGE ON TOPIC.TOPIC_ID = MESSAGE.TOPIC_ID
WHERE TOPIC.TOPIC_ID = top_id
GROUP BY TOPIC.TOPIC_ID, TOPIC.TITLE;

This stored proc shows Board_ID, Borad Name, topic_Id , topic.title topic.stared-by, MESSAGE COUNT, Topic description Under each BOARD:

PARAMETERS bid Long;
SELECT BOARD.BOARD_ID, BOARD.NAME, TOPIC.TOPIC_ID, TOPIC.TITLE, TOPIC.STARTED_BY, (SELECT Count(MESSAGE.MESSAGE_ID) AS MSG_COUNT FROM MESSAGE where MESSAGE.TOPIC_ID= TOPIC.TOPIC_ID
GROUP BY TOPIC.TOPIC_ID) AS msg_count, TOPIC.DESCRIPTION
FROM BOARD INNER JOIN TOPIC ON BOARD.BOARD_ID = TOPIC.BOARD_ID
WHERE (((BOARD.BOARD_ID)=[bid]));

This stored proc shows that board_ID, topic_ID. descrption, stared-by and the counts of messeage under each topic

SELECT t.BOARD_ID, t.TOPIC_ID, t.TITLE, t.DESCRIPTION, t.STARTED_BY, (SELECT Count([MESSAGE_ID]) from MESSAGE where MESSAGE.TOPIC_ID=t.TOPIC_ID) AS msg_cnt
FROM TOPIC AS t;

Thank you very much"

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2003-09-25 : 08:12:40

quote:

Could u please convert the follwing stored procedures from my mdb database to a store procedure in sql server?

No, but if you make some effort we will help you.

Damian

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources