Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2002
    Posts
    21

    Unanswered: using multiple select statement in stored procedure

    I need to get the output from a select statement, which could be one or more records, and use them as criteria for another select statement, and then use the output from the second select as criteria for a third select, and so on and so forth, until the select returns nothing. What I need to get from the stored procedure is all the output combined together.

  2. #2
    Join Date
    Jun 2002
    Location
    USA
    Posts
    21
    Hi ,

    Use Joins in your SQL Select statements . You can achieve this in one single query.

    For more info: See Books Online to know the different types of join and examples.

    Have Fun.

    Varad01

  3. #3
    Join Date
    May 2002
    Location
    Timbaktu
    Posts
    185
    You can try to select the rows in a Temp Table

  4. #4
    Join Date
    Aug 2002
    Posts
    21
    All my select is against one table. The table has id field and sponsorid field. "Select id from table Where sponsorid=@id".

  5. #5
    Join Date
    Apr 2002
    Location
    England
    Posts
    21
    I presume that @id is the criteria that is going to change, I would create a temp table to load the results into and then do a while loop which checks an if exists on the select statement you are going to run. Then in the while loop insert the results from the select statement into the temp table. Finally carry out the alteration on @id. Close the while loop.

    Once out of the while loop select all from the temp table (maybee using distinct to avoid row replication).

    Hope this makes some kind of sense

  6. #6
    Join Date
    Aug 2002
    Posts
    15
    I couldn't decipher much of the details from your message of exactly what you are trying to do, but you can join a table on itself. You can also join on a subselect. The last one is a great way to get to a record based on an aggregate function.

    Take this example... Say you have a table that logs everytime someone calls a proc. And you want to see the most recent record for that call. Many people would write the SQL this way:

    SELECT *
    FROM ProcAuditTbl
    WHERE ProcName = 'spTryMe'
    AND LastCalled = MAX(LastCalled)

    I used to get students on this one back when I taught SQL in college. you can't do the syntax above. You can't include an aggregate function like that in the where clause. The easiest way for this is to use a sub-select as such:

    SELECT *
    FROM ProcAuditTbl
    WHERE ProcName = 'spTryme'
    AND LastCalled = (SELECT MAX(LastCalled) FROM ProcAuditTbl WHERE ProcName = 'spTryMe')

    So, back to your issue... Depending on exactly what you want to gather, you could use sub-selects or join the table to itself. Last resort would be to use a temp table. In 99% of circumstances, sub-selects and joins will out perform a temp table.

  7. #7
    Join Date
    Aug 2002
    Posts
    21
    sub select sounds great and can solve my problem. this may sound silly, how do you join a table to itself?
    Thank you, guys.

  8. #8
    Join Date
    Aug 2002
    Posts
    15
    Originally posted by samcheng
    sub select sounds great and can solve my problem. this may sound silly, how do you join a table to itself?
    Thank you, guys.
    Joining a table to itself is pretty simple. Let's say you have a table that is a tree structure. Here's an example:

    CREATE TABLE tblTree
    (TreeID int IDENTITY,
    TreeDesc varchar(50) NOT NULL,
    ParentTreeID int NULL)

    For items at the top of the tree, the ParentTreeID is null. For all other records, the ParentTreeID is the TreeID of the immediate parent of the record. Here are some example records:

    TreeID TreeDesc ParentTreeID
    1 Fruits null
    2 Apples 1
    3 Bananas 1
    ...

    Now say you want to get all records from the table under the 'Fruit' branch of this tree. Here is one way:

    DECLARE @FruitID int

    SELECT @FruitID = TreeID
    FROM tblTree
    WHERE TreeDesc = 'Fruits'

    SELECT TreeID, TreeDesc
    FROM tblTree
    WHERE ParentTreeID = @FruitID

    You can also do it with a sub-select:

    SELECT TreeID, TreeDesc
    FROM tblTree
    WHERE ParentTreeID = (SELECT TreeID FROM tblTree WHERE TreeDesc = 'Fruits')

    Finally, you can do it in a single statement by joining to itself:

    SELECT c.TreeID, c.TreeDesc
    FROM tblTree c
    JOIN tblTree p ON p.TreeID = c.ParentTreeID AND p.TreeDesc = 'Fruits'

    This is a simple example, but I hope it helps you better understand.

Posting Permissions

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