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

Need Help with Stored Procedure and Table Relationships

Discuss Need Help with Stored Procedure and Table Relationships in the MS SQL Development forum on Dev Shed.
Need Help with Stored Procedure and Table Relationships MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
You eat, breathe and sleep innovation. Build your mobile intelligence with BlackBerry® experts this July. Register Today!
  #1  
Old February 24th, 2004, 06:36 PM
lito's Avatar
lito lito is offline
MACTEP /\OMACTEP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Location: Seattle, WA
Posts: 150 lito User rank is Private First Class (20 - 50 Reputation Level)lito User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 6 h 46 m 51 sec
Reputation Power: 6
Question Need Help with Stored Procedure and Table Relationships
Hi,
I am in desperate Need of Help with Stored Procedure and Table Relationships. I am using MS SQL 2000 and I am new to stored Procedures.

What I have is 2 tables and I am not sure how to write this stored procedure. My Tables look like this...

TABLE catList
--------------------
catNo | catName
--------------------
1 | Tennis
2 | Basketball
3 | Soccer

TABLE students
----------------------
id | name | sport | ...
----------------------
1 | Jeff | 3 | ...
2 | Lisa | 1 | ...
3 | Bob | 2 | ...
4 | Anna | 1 | ...

How do I program my stored procedure so that
instead of seeing 1,2 or 3 for sport I would see
Tennis, Basketball, or Soccer

Please help if you know the answer or a good tutorial that explains how to do that.

Thanks,
Lito
__________________
Sometimes just a few hours of trial and error debugging can save minutes of reading the manual.
Last edited by lito : February 24th, 2004 at 06:43 PM.
Reply With Quote
  #2  
Old February 24th, 2004, 10:16 PM
nhminh nhminh is offline
Registered User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2004
Posts: 2 nhminh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Quote:
Originally Posted by lito
Hi,
I am in desperate Need of Help with Stored Procedure and Table Relationships. I am using MS SQL 2000 and I am new to stored Procedures.

What I have is 2 tables and I am not sure how to write this stored procedure. My Tables look like this...

TABLE catList
--------------------
catNo | catName
--------------------
1 | Tennis
2 | Basketball
3 | Soccer

TABLE students
----------------------
id | name | sport | ...
----------------------
1 | Jeff | 3 | ...
2 | Lisa | 1 | ...
3 | Bob | 2 | ...
4 | Anna | 1 | ...

How do I program my stored procedure so that
instead of seeing 1,2 or 3 for sport I would see
Tennis, Basketball, or Soccer

Please help if you know the answer or a good tutorial that explains how to do that.

Thanks,
Lito

Select id, name, catname from students, catlist where catlist.catNo = students.sport
simplier if use View instead
You just new a store prod and paste above code after AS and before GO
Reply With Quote
  #3  
Old February 25th, 2004, 11:43 AM
lito's Avatar
lito lito is offline
MACTEP /\OMACTEP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2002
Location: Seattle, WA
Posts: 150 lito User rank is Private First Class (20 - 50 Reputation Level)lito User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: 6 h 46 m 51 sec
Reputation Power: 6
Thanks for your suggestion, but I don’t think that this would work for me. The tables I gave is just a basic example of how my db is setup. The "stored proc" I need to put together would pull from about 6-8 different tables and within those tables I would have foreign key values to a tables that holds a more descriptive word. Maybe this would make more sense...
althoug it doesn't work this should give you a better idea of what I'm up against. Thanks again, Lito
PHP Code:
 CREATE PROCEDURE sp_GetRecord (@eID INT) AS 
BEGIN
DECLARE @status VARCHAR(20)
DECLARE @location VARCHAR(20)
DECLARE @owner VARCHAR(20)

SET @status = "(SELECT stsName FROM tblStatusCodes WHERE stsID = animal.status)"
SET @location = "(SELECT locName FROM tblLocations WHERE locID = loc.loc_id)"

IF loc.loc_id == "C" /* If owned by a company */
    SET @owner = "(SELECT compName FROM tblCompany WHERE id = loc.loc_id)"
ELSE
    SET @owner = "(SELECT userName FROM tblUser WHERE id = loc.loc_id)"
END IF

  SELECT 
    @owner AS owner, @location AS location, @status AS status, photo_reg, photo_thumb, notes
   FROM 
    ctsIndex animal,         
    ctsJrn_Location loc,
    ctsJrn_Owner ow    
  WHERE
    animal.id=@eID OR     
    loc.eID = @eID OR
    ow.eID = @eID    
END 
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Need Help with Stored Procedure and Table Relationships

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
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 5 hosted by Hostway.