Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: double loop in stored procedure

    Dear All,

    Im working on a stored procedure that meant to mail out users some of their action items daily.

    The procedure has a double loop, first the user ids and user email addresses are selected into a table, then the outer loop cycles through the user ids and selects relevant action items to another table. The inner loop then cycles through these action items and at the end of each outer loop a string is mailed out.

    Problem is that as the outer loop selects the relevant items for a user, the table holding the action items basically gets filled with more and more records and the inner loop then adds every item in the table to the string that gets mailed out, ending up with more and more items going to all the users.

    I have tried to delete all records from the actionItems table at the end of each outer loop after the content of the action Items are mailed out, however this seems to keep the actionItems table empty at all times.

    Not sure if this description is clear enough but I cant see where Im going wrong in terms of approach.

    Any ideas?

  2. #2
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    I would suggest posting your SP here, so that we can check out what is going on in the T-SQL code.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Any TSQL post that uses the word "loop" that frequently can't be good.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Mar 2005
    Posts
    92
    Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. Cheers

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whatever. Continue merrilly down your path to the dark side...
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by gorgenyi
    Actually it's sorted, just added a variable that picks up the id of the last action item being added to the e-mail, then the count to add action items to the next e-mail starts from there + 1 AND also I had set this counter to 0 at the start as it would not start the loops while not initialised. Cheers
    Have you tried dropping charcoal briquettes or real cotton into fuming nitric acid?

    -PatP

Posting Permissions

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