Wow. Where do we start.
1) Don't use cursors. This procedure can be rewritten using standard SET-based SQL commands. First, do the update on current date records where they match on the correct criteria (sequence number). Then do the insert for the records which do not match. Then do an update for the 7 days back batch. Then lastly insert the remaining records. I will try an example at the end of this post (although it's tough since it's not really in english so I might guess at a few things)
2) Don't use SP_ as the name of the procedure. Not a huge deal, but you should avoid this naming convention.
3) Get rid of initial SELECT without SUB-SELECTS in WHERE clause...you can put these into temporary variables
OK, here goes:
CREATE PROCEDURE UpdateWorkPlan AS
DECLARE
@Item VARCHAR(20)
, @Lijn VARCHAR (2)
, @Land VARCHAR (4)
, @Bestemming VARCHAR(4)
, @AantalPlan DECIMAL(6,0)
, @AantalWerkelijk DECIMAL(6,0)
, @SeqNr DECIMAL(18,0)
, @TempYear VARCHAR(4) -- Might be wrong datatype...
, @TempPeriod VARCHAR(10) -- Might be wrong datatype...
, @TempWeek VARCHAR(10) -- Might be wrong datatype...
/* Get temp variables from today's date */
SELECT
@TempYear = mfyear
, @TempPeriod = mfperiod
, @TempWeek = mfweek
FROM INFO_NEW_MFPERIODE
WHERE MFDag = CONVERT(VARCHAR(10),GETDATE(),120))
/* Update today's date matches and other criteria */
UPDATE WP
SET WP.AANTALPLAN = INFO_ARTIKEL_PLAN.AANTALPLAN
FROM INFO_WORK_PLAN WP
INNER JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
/* Next do inserts for today's date batch */
INSERT INTO INFO_WORK_PLAN
(ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN, AANTALWERKELIJK,SEQNR)
SELECT
INFO_ARTIKEL_PLAN.ITEM
, INFO_ARTIKEL_PLAN.LIJN
, INFO_ARTIKEL_PLAN.LAND
, INFO_ARTIKEL_PLAN.BESTEMMING
, INFO_ARTIKEL_PLAN.AANTALPLAN
, INFO_ARTIKEL_PLAN.AANTALWERKELIJK
, INFO_ARTIKEL_PLAN.SEQNR
FROM INFO_WORK_PLAN WP
RIGHT JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
AND WP.SEQNR IS NULL
/* reset temp variables from 7 days ago */
SELECT
@TempYear = mfyear
, @TempPeriod = mfperiod
, @TempWeek = mfweek
FROM INFO_NEW_MFPERIODE
WHERE MFDag = CONVERT(VARCHAR(10),GETDATE() - 7,120))
/* Update today's date matches and other criteria */
UPDATE WP
SET WP.AANTALPLAN = INFO_ARTIKEL_PLAN.AANTALPLAN
FROM INFO_WORK_PLAN WP
INNER JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
/* Next do inserts for today's date batch */
INSERT INTO INFO_WORK_PLAN
(ITEM,LIJN,LAND,BESTEMMING,AANTALPLAN, AANTALWERKELIJK,SEQNR)
SELECT
INFO_ARTIKEL_PLAN.ITEM
, INFO_ARTIKEL_PLAN.LIJN
, INFO_ARTIKEL_PLAN.LAND
, INFO_ARTIKEL_PLAN.BESTEMMING
, INFO_ARTIKEL_PLAN.AANTALPLAN
, INFO_ARTIKEL_PLAN.AANTALWERKELIJK
, INFO_ARTIKEL_PLAN.SEQNR
FROM INFO_WORK_PLAN WP
RIGHT JOIN INFO_ARTIKEL_PLAN
ON WP.SEQNR = INFO_ARTIKEL_PLAN.SEQNR
WHERE
INFO_ARTIKEL_PLAN.[YEAR] = @TempYear
AND INFO_ARTIKEL_PLAN.[period] = @TempPeriod
AND INFO_ARTIKEL_PLAN.[week] = @TempWeek
AND WP.SEQNR IS NULL
/*Next we delete all records which no longer exists in the info_artikel_plan*/
DELETE FROM info_work_plan WHERE NOT EXISTS (SELECT * FROM info_artikel_plan WHERE info_artikel_plan.seqnr = Info_work_plan.seqnr )
Oh, and I would highly recommend putting a transaction around the whole thing... 🙂
Hope this helps,
Jay
Edited by - jpipes on 05/22/2003 2:42:08 PM