|
Hello, I am working on a web service for retreving news updates and need some help with a stored procedure.
I have 3 tables
1. tblstory ( to hold the news article, headline, date etc)
fldstoryID (PK)
fldheadline
fldstory
flddate
fldtype
2. tblsymbol ( to hold stock symbol, company name, industry)
fldsymbolID (PK)
fldsymbol
fldcompanyname
fldindustry
3. tblstorysymbol ( to hold the storyID and symbolID - many to many)
fldstorysymbolID (PK)
fldstoryID (FK)
fldsymbolID (FK)
I want to be able to have a stored procedure to insert a new story into the above tables by sending it the symbol, headline, story, date, and category.
A. It has to create a tblstory record with the passed headline, story, date and category
B. Lookup the passed stock symbol in tblsymbol and return the symbolID of that stock symbol if it exists otherwise add the stock symbol to the tblsymbol
C. Create a tblstorysymbol record with the storyID and stockID
If you have any suggestions I would appreciate it. This is being done in MSSQL 7.
Here is a copy of the view I use in case it helps-
SELECT dbo.tblsymbol.fldsymbol AS Symbol, dbo.tblstory.fldheadline AS Headline, dbo.tblstory.fldstory AS Story, dbo.tblstory.flddate,
dbo.tblstory.fldtime
FROM dbo.tblsymbol LEFT OUTER JOIN
dbo.tblstorysymbol ON dbo.tblsymbol.fldsymbolID = dbo.tblstorysymbol.fldsymbolID FULL OUTER JOIN
dbo.tblstory ON dbo.tblstorysymbol.fldstoryID = dbo.tblstory.fldstoryID
WHERE (dbo.tblsymbol.fldsymbol = '[GOOG]')
Thanks in advance
|
