Results 1 to 6 of 6

Thread: Temp Table?

  1. #1
    Join Date
    Oct 2003
    Posts
    12

    Unanswered: Temp Table?

    I have 2 sql tables. One containing sales agents and sub-agents (Up to 5 or 6 levels deep), the other containing customers. Each customer has an agent associated with them.

    Agents Table
    - AgentId
    - ParentId

    Customers Table
    - CustomerId
    - AgentId

    What I need to do is when you select an agent, I need to return all of that agents customers AND all of that agents sub-agents customers, and down the tree like that.

    I think the way to do this is to use a Stored Procedure to create a TEMP table and then select theh top level agent, then continue a loop to get their subagents, then each of their subagents, etc.. and put the results all in this temp table.

    I then can call the temp table and retrieve the customers where the agent id matches any of the agents in the temp table.

    Am I thinking the correct way on this? I have never even built a stored procedure before, so can anyone help me with any tutorials or syntax for doing this? I still haven't been able to google anything yet.

    Thanks!

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, that is the best method, in my opinion.

    I have used recursive code to do this in that past, but found that it was not as efficient or robust as the "Accumulator Table" method you describe.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    May 2004
    Location
    Columbus, OH
    Posts
    59

    Syntax/Tutorial

    Well.....as far as syntax is concerned, you could go into Querry Analyzer, select the Master table, select Stored Procedures, and very carefully pick a procedure, right click and select "Script to New Window as...Create". That should let you see enough to get some ideas.

    Just be very....very....very...carefull. Messing up one of the SP's on the Master table would be very bad.

    As far as turorial....I always just looked at a stored procedure as a group of T-SQL code that I didn't have to type in over and over again.


    bEH

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here is some skeleton code:

    Code:
    create procedure ChildKeys(@SeedKey as int)
    as
    
    declare	@Accumulator table (KeyValue int)
    
    insert into @Accumulator (KeyValue)
    select	KeyValue
    from	YourTable
    where	YourTable.KeyValue = @SeedKey
    
    while	@@RowCount > 0
    insert into @Accumulator (KeyValue)
    select	YourTable.KeyValue
    from	YourTable
    	inner join @Accumulator Accumulator on YourTable.ParentKeyValue = Accumulator.KeyValue
    where	not exists (select * from @Accumulator CurrentRecords where CurrentRecords.KeyValue =  YourTable.KeyValue)
    
    select	KeyValue
    from	@Accumulator
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2003
    Posts
    12
    So something like this:
    Code:
    create procedure dbo.dt_AgentTree(@AgentId as int)
       as
       
       declare	@Accumulator table (contact_id int)
       
       insert into @Accumulator (contact_id)
       select	contact_id
       from	vs_contacts v
       where	v.contact_id = @AgentId
       
       while	@@RowCount > 0
       insert into @Accumulator (contact_id)
       select	v.contact_id
       from	vs_contacts v
       	inner join @Accumulator Accumulator on v.parent_id = Accumulator.contact_id
     where	not exists (select * from @Accumulator CurrentRecords where CurrentRecords.contact_id = v.contact_id)
       
       select	contact_id
       from	@Accumulator

  6. #6
    Join Date
    Oct 2003
    Posts
    12
    It seems to be working. Thanks a lot! You really made my day.

Posting Permissions

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