Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Post Unanswered: writting store procedure--- help pls

    need help with store procedure...

    have 3 tbls.
    tblComp(PK CompId(int 4), compName, dateCreat(datetime 8)--no null)

    tblContact [PK contId(int 4), Name, MemoCount(int 4), dateEnter(datetime 8)--no null]

    tblMemo[pk memoId (int 4), {FK} contId(int 4), {FK} compId (int 4), memo (varchar 100)]

    Need to Write a stored procedure that takes a parameter of a compId. It then retrieves the company record, any contact records associated with the company, any memo associated with the company, and any memo associated with the contacts. The stored procedure returns four result sets (company record, contact records, memo associated with company, and memo associated with contacts). For the memo result sets, have to make sure records are listed once and only once.

    thanks
    mat..

  2. #2
    Join Date
    Feb 2003
    Location
    Montreal, Canada
    Posts
    117

    Re: writting store procedure--- help pls

    CREATE PROCEDURE [dbo.][TestProcedure] (@CompID INT)
    AS
    BEGIN
    SELECT * FROM tblComp WHERE CompID=@CompID
    SELECT * FROM tblContact WHERE ContID IN(SELECT ContID FROM tblMemo WHERE CompID=@CompID)
    SELECT memo FROM tblMemo WHERE CompID=@CompID
    SELECT memo FROM tblMemo WHERE ContID IN(SELECT ContID FROM tblMemo WHERE CompID=@CompID) AND memoID NOT IN(SELECT memoID WHERE CompID=@CompID)
    END

    The 4 select phrases correspond to your 4 recordsets.(company record, contact records, memo associated with company, and memo associated with contacts).

    Originally posted by mark00189
    need help with store procedure...

    have 3 tbls.
    tblComp(PK CompId(int 4), compName, dateCreat(datetime 8)--no null)

    tblContact [PK contId(int 4), Name, MemoCount(int 4), dateEnter(datetime 8)--no null]

    tblMemo[pk memoId (int 4), {FK} contId(int 4), {FK} compId (int 4), memo (varchar 100)]

    Need to Write a stored procedure that takes a parameter of a compId. It then retrieves the company record, any contact records associated with the company, any memo associated with the company, and any memo associated with the contacts. The stored procedure returns four result sets (company record, contact records, memo associated with company, and memo associated with contacts). For the memo result sets, have to make sure records are listed once and only once.

    thanks
    mat..
    Steve

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    ... and also make sure to refer books online for such syntax information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •