MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed ForumsDatabasesMS SQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old November 8th, 2004, 07:13 PM
haffej haffej is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2004
Posts: 22 haffej User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 41 m 46 sec
Reputation Power: 0
variables for table names in stored proc

i'm trying to create a stored procedure that takes 2 input parameters (taken from a querystring): a table name, and a number that needs to be checked whether it exists within that table. yes, i have different tables, one for each forum, so they will have the same structures -- i'm figuring this approach will result in faster page-load times if the tables have less in them. if everyone here tells me that having one table for all the forums will work fine, i'll just go that route, but if not, here's my procedure:

Create Procedure VerifySubjectNum
(@forum_ID VARCHAR(10), @subject_num INT)
As
If Exists
(SELECT subject_num FROM @forum_ID WHERE subject_num = @subject_num)
Return 1
Else
Return 0

when i try to create this, i get an error saying @forum_ID must be declared... why doesn't it work within the select for the EXISTS?

Reply With Quote
  #2  
Old November 9th, 2004, 03:50 AM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,784 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37
Use one table for all forums. If you have proper indexing it will not be any performance drawback.

Reply With Quote
  #3  
Old November 9th, 2004, 09:28 AM
haffej haffej is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2004
Posts: 22 haffej User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 41 m 46 sec
Reputation Power: 0
can you give me an example of what proper indexing would be for one table that contains 4 different forums?

Reply With Quote
  #4  
Old November 9th, 2004, 11:22 AM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,784 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37
It depends on how the table is defined and what queries you are using. You would still need indexing if you have multiple tables.

Reply With Quote
  #5  
Old November 10th, 2004, 12:45 PM
haffej haffej is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2004
Posts: 22 haffej User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 41 m 46 sec
Reputation Power: 0
okay, so here's what i'm playing with right now...

i'm trying to use 1 table for 4 forums. each row is a single post, and its location is determined by two columns: forum_ID (a short varchar, which will be 1 of 4 possiblities) and subject_num (int, which will take on the subject number of its parent post). it also has a column post_num (int) which is an identity.

queries will be used to retrieve, most importantly, a list of all subjects within a forum, and a list of all posts within a subject within a forum.

so i played around with indexing/keys (which i don't know much about, admittedly), and made forum_ID a clustered key, and subject_num a regular key. i can't make either a primary key since their data will not be unique.

am i on the right track?

Reply With Quote
  #6  
Old November 10th, 2004, 02:45 PM
swampBoogie swampBoogie is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Jan 2003
Location: Paris Uppland
Posts: 1,784 swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level)swampBoogie User rank is Sergeant Major (2000 - 5000 Reputation Level) 
Time spent in forums: 2 Weeks 4 Days 21 h 42 m 49 sec
Reputation Power: 37
Quote:
am i on the right track?


Definitely with not having multiple tables for similar data.
You may benefit from having a combinded index on subject and forum instead of just subject.

Reply With Quote
  #7  
Old November 10th, 2004, 05:08 PM
haffej haffej is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Nov 2004
Posts: 22 haffej User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 41 m 46 sec
Reputation Power: 0
so , keep the forum_ID clustered index, and make the index i have for subject_num also include forum_ID?

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMS SQL Development > variables for table names in stored proc


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway