Hello all, I am new with stored procedures and i was wondering how and if this could be done.

What i have currently in my database is two tables. 1 is the master table that tells me what categories a particular author has posted in, and then a whole bunch of sub tables that corrispond to the particular categories.

What i need to do is somehow exec 1 stored procudure that gives me all the category tables that a particular author has posted in, then loop through that recordset and populate a master record set that has all the common attributes from the different category tables such as Topic, MessageBody and so forth.

SO in essance,

i need to do something like

"Select Category_TableName From MasterCategoryList where AuthorID=@AuthorID"

the loop through that and do something like

"Select Topic, MessageBody FROM @TableName Where AuthorID=@AuthorID ORder by Date Desc"

any help is greatly appretiated.

if you are quering on tables that are stored as a data in certain table so let me imagine that wonderful database design?????
"every thing is possible but we must choose the shortest ways to do as we can"
....................
I need from you to explain more or i am understand some thing else

Ahmed Ali
Software Developer

I'm sorry i don't understand your question. Can you please elaborate? Do you want some table names or table design structure so that you can gain a better understanding?

Hi flyin,

Yes, I am too unable to make out the entire picture from your original post of what you are trying to do. May be you can post the structure of related tables and explain what is need to be done, would help us understand better on that.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Hello All,

Sorry if i was too vague on my post. Here is my attempt to clerify things.

I have about 12-15 different tables that store the information pertaining to the articles a person
has written within that particular category.

 There exists 1 master table that stores the name of the Categories, The CategoryID, Description,
 and also the TableName of where the data for that category can be found.

 So for instance, lets call the master table

 Table: ArticleMaster
 Fields:
 ID - CategoryID
 Name - Category Name as displayed to user
 Description - Short description of the category and what topics get posted into this category
 TableName - The Name of the table in the database where this categories entries are stored


 Now for the other 12-15 tables, lets take 2 of them for an example:

 TableName: Entertainment
 Fields:
 ID - This is the ID for that particular post, this is not the same as the the one in ArticleMaster
 AuthorID - ID of the person who posted within this category
 PostText - This is the article they submitted.
 EntertainmentSupported - this field is something that the admin can set to true if they like the post.
                                    and it will appear in the favorites list for the site.

 TableName: Politics
 Fields:
 ID - This is the ID for that particular post, this is not the same as the the one in ArticleMaster
 AuthorID - ID of the person who posted within this category
 PostText - This is the article they submitted.
 RepublicanSupported - if a republican admin of the site likes this post, it will be featured in the
                                republican section of the site.
DebateQuestion - If an admin of the site deems this as a fair question to debate amongst parties, he can
                        set this value to true and it will be one of the questions in the weekly debate.
AlreadyDebated - Bit value that lets the debate know if the question has been debated yet or not.


And now, A simple scenario that will help introduce the last table.
Scenario: Lets say that we are looking at a particular author's profile. And within the profile
it lists all of the posts he/she has done within the last 30 days. Well, the way the table structure
has been made, i would have to first make a query to the MasterArticle Table to gain information on the
Categories, the do about 12-15 queries on each of those tables to see if i have a match for this
particular AuthorID which i do not want to do.

So which brings me to my last table.

TableName: AuthorPostIndex

Fields:

AuthorID - Obvious by now
CategoryID - this is the ID field from the MasterArticleTable
PostCount - this gets incremented/decremented every time they post/remove one of their articles.

The purpose of this table is such that i can join this table to MasterArticle Table where AuthorID
= the current AuthorID so it only brings me back a list of Categories that this particular author
has posted within.

Now to my question.

In a stored procedure:

Create Procedure GetAuthorIndex( @AuthorID )

i want to do 3 main things.

1: Query: Join the AUthorPostIndex table with the ArticleMaster Table with the criteria of AuthorID
and get a list of categories that this author has posted in.

2: with the Categories that resulted: i want to build 1 big string that represents a unionized query
that unions all 12-15 tables. Lets take the two tables out of the 15 that i was speaking on and make a
query.

I am doing psuedo code here because i do not know how to write loops and what not in a stored procedure.

-- First do The query Specified in step 1 to get me a list of all categories this author has posted in

Set @SQL = ''
Set @DoUnion = 0
Do While( !EOF )
    if ( @DoUnion = 1 ) then
        Set @SQL = @SQL + ' UNION '
    end if

    Set @CategoryID = RecordSet("ID")
    if( @CategoryID = 'The CategoryID for EnterTainment ) then
        Set @SQL = @SQL + SELECT @CategoryID as CategoryID, ID as PostID, AuthorID, PostText,
                        EnterTainmentSupported, NULL as RepublicanSupported, NULL as DebateQuestion,
                        NULL as AlreadyDebated FROM Entertainment WHERE AuthorID = @AuthorID
    elseif (@CategoryID = 'The CategoryID for Politics) then
        Set @SQL = @SQL + SELECT @CategoryID as CategoryID, ID as PostID, AuthorID, PostText,
                        NULL as EnterTainmentSupported, RepublicanSupported, DebateQuestion,
                        AlreadyDebated FROM Entertainment WHERE AuthorID = @AuthorID
    end if
    Set @DoUnion = 1
LOOP

Exec(@SQL)

Go

3: Obvious step: run the unionized query and return it's result

is this more clear? i hope so thanks all!

For this you will have to use a CURSOR within your stored proc to loop through and get all that details for any single author, which I feel is a bit complicating things and would have as many IFs (checking for CATEGORY) as the number of tables you have(12-15). Also those 12-15 tables seem to have different structure from the other.

Instead I would say you can blindly go ahead with constructing a set of 12-15 SELECT statements all unionized with UNION operator and criteria being the AUTHORID for each, and EXEC() at the end, which would result in the same way.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

i think you could fill a hash table then do join with the entertainment table
step 1:
select categoryId into #CatForAuthor
from AUthorPostIndex join ArticleMaster on ArticleMaster.AuthorID= AUthorPostIndex.AuthorID
where AUthorPostIndex.AuthorID=@AuthorID

step 2:

select #CatForAuthor.CategoryID, ID as PostID, AuthorID, PostText,
                        EnterTainmentSupported, NULL as RepublicanSupported, NULL as DebateQuestion, AuthorID
,NULL as AlreadyDebated FROM Entertainment join #CatForAuthor
on #CatForAuthor.CategoryID=Entertainment.CategoryID
WHERE AuthorID = @AuthorID




Ahmed Ali
Software Developer