Loop within a Stored Proc...

  • Say I select values within a stored proc like so

    SELECT EventID from Event where Column1 = 1

    And it returns

    1

    2

    3

    How can I then say

    INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,1)

    INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,2)

    INSERT INTO ProjectEvent (ProjectID, EventID) Values (1,3)

    I won't know how many EventIDs I will be getting back in the first SELECT. I could do a for each in the C#, but that seems silly. There has to be a way to do within the SP. Thoughts? Thanks for looking!

    Harry C

  • Now to Calculate the EstimatedShipDate...any help is greatly appreciated. Thanks

    INSERT INTO ProjectEvent(ProjectID, EventID, EstimatedCompletionDate, LeadTime)

    SELECT @ProjectID, CompanySeasonProductTemplate.EventID, (Calculate Estimated CompletionDate Here), Event.LeadTime

    FROM CompanySeasonProductTemplate INNER JOIN

    Event ON CompanySeasonProductTemplate.EventID = Event.EventID

    WHERE (CompanySeasonProductTemplate.CompanyID = @CompanyID)

    AND SeasonID Is Null

    AND ProductCategoryID Is Null

    ORDER BY EventOrder

    The Select Statement above would return a record like so

    ProjectID :: EventID :: EstimatedCompletionDate :: LeadTime

    1 :: 1 :: :: 10

    1 :: 2 :: :: 20

    1 :: 3 :: :: 5

    So, what I need to do here is to take @CustomerShipDate, which is passed into the stored proc, the count the days backward. So, if @CustomerShipDate were 07/25/2005, and I were calculating for EventID 3. I could say DateAdd(day, LeadTime(which is 5), @CustomerShipDate).

    But, now, how would I get the rest of the values?? Because now, for EventID 2, I need to say

    DateAdd(day, LeadTime(EventID 3 LeadTime which is 5 MINUS EventID 2 LeadTime which is 20 ), @CustomerShipDate)

    Does this makes sense? Thanks for looking

  • I don't fully understand your logic for all cases but this may put you in the right Track:

    select

     Project,

     EventID,

     DateAdd( day,

           (select sum(Leadtime)

                          from yourtable t2

                          where t1.Project =t2.Project

                                and

                                t1.EventID <= t2.EventID)  --TotalLeadTime

      ,  @CustomerShipDate ) ShipDate

    from Yourtable t1

     


    * Noel

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply