• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1313
  • Last Modified:

Stored Procedure Help - Nested Loop & Update

I would like to create a MS SQL 2005 stored procedure but unclear on the syntax.  I have three queries.  The firstquery is returning a list of offer ids.  I would like to loop through the secondquery based on the recordcount of the firstquery.  From the seconquery, i would like to loop the results over an update statement.  The loops will need to be nested and not sure how to reference the queries from other queries...  Please refer to my example below:

// GET ALL OFFERS (firstquery)

@sup_id (int)

SELECT o.offer_id, o.length, o.depart_day, o.sup_id, p.ship_code
FROM Offer o
INNER JOIN product p
      ON o.product_id = p.product_id
WHERE (o.sup_id = @sup_id)
AND drop_day >= getDate();


// LOOP OVER OFFERS

WHILE @?
BEGIN

// secondquery

SELECT o.offer_id, pi.arrive_time, pi.depart_time, pi.voyage_code, pi.depart_date, pi.arrive_date
FROM Offer o
INNER JOIN product p
      ON o.product_id = p.product_id
INNER JOIN princess_voyage_codes pvc
      ON p.ship_code = pvc.ship
INNER JOIN princess_itinerary pi
      ON pi.voyage_code = pvc.voyage_code      
WHERE (o.sup_id = firstQuery.sup_id)
AND (p.ship_code = 'firstQuery.ship_code')
AND (o.offer_id = firstQuery.offer_id)
AND (pvc.ship = 'firstQuery.ship_code')
AND (pvc.sea_days = firstQuery.length)
AND (pvc.depart_date = 'firstQuery.depart_day')
ORDER BY pi.depart_date


// LOOP RESULTS of secondquery AND UPDATE TABLE

WHILE @?
BEGIN
UPDATE itinerary
SET arrival = 'secondQuery.arrive_time',
departure = 'secondQuery.depart_time'
WHERE offer_id = firstQuery.offer_id;

END




END


Thank you and I appreciate any input.

Terry
0
splendorx
Asked:
splendorx
  • 2
2 Solutions
 
sabeeshCommented:
You can use cursors for this
0
 
Ted BouskillSenior Software DeveloperCommented:
Well read the following first paragraph in regard to cursors: http://www.sql-server-performance.com/cursors.asp

At first glance I think you can do this with one query.

UPDATE itinerary
    SET arrival = 'OffersQuery.arrive_time',
        departure = 'OffersQuery.depart_time'
FROM itinerary
(
    SELECT
        o.offer_id,
        pi.arrive_time,
        pi.depart_time
    FROM Offer o
        INNER JOIN product p ON o.product_id = p.product_id
        INNER JOIN princess_voyage_codes pvc
            ON p.ship_code = pvc.ship AND o.length = pvc.sea_days AND o.depart_day = pvc.depart_date
        INNER JOIN princess_itinerary pi ON pi.voyage_code = pvc.voyage_code      
    WHERE
      (o.sup_id = @sup_id)
      AND
      drop_day >= getDate();
) OffersQuery
WHERE offer_id = OffersQuery.offer_id;
0
 
splendorxAuthor Commented:
thank you for the help!
0
 
Ted BouskillSenior Software DeveloperCommented:
Cheers
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now