1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69
| CREATE PROCEDURE PS_nLog AS
DECLARE
@sscc char(18),
@counttotalsscc int,
@countexisteoui int,
@countexistenon int,
@countinsert int,
@dtdate datetime,
@dttime datetime,
@strOrder char(10),
@strArtNr char(20),
@nPalNr int,
@strMDH varchar(8),
@nFinish int
SET @counttotalsscc = 0
SET @countexisteoui = 0
SET @countexistenon = 0
SET @countinsert = 0
SET NOCOUNT ON
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
/****************************************************************/
/* Déclaration du curseur C_Export */
/****************************************************************/
DECLARE C_Export CURSOR FOR
SELECT strSSCC
FROM tPalProd
WHERE nFinish IN (1,3) AND nLog = 0
OPEN C_Export
FETCH NEXT FROM C_Export
INTO @sscc
WHILE @@FETCH_STATUS = 0
BEGIN
SET @counttotalsscc = @counttotalsscc + 1
IF (SELECT COUNT(*)
FROM SEBAS009.Aproz.dbo.tpalprod
WHERE strsscc = @sscc) > 0
BEGIN
SET @countexisteoui = @countexisteoui + 1
PRINT 'SSCC: ' + @sscc + ' existe déjà sur SEBAS009'
UPDATE tPalProd SET nlog = -1 WHERE strSSCC = @sscc
END
ELSE
BEGIN
SELECT @dtdate = dtdate, @dttime = dttime, @strOrder = strOrder,
@strArtNr = strArtNr, @nPalNr = nPalNr,
@strMDH = strMHD, @nFinish = nFinish
FROM tPalProd
WHERE strSSCC = @sscc
INSERT INTO SEBAS009.Aproz.dbo.tPalProd
VALUES (@dtDate, @dtTime, @sscc, @strOrder, @strArtNr, @nPalNr,
@strMDH, 0, @nFinish)
IF @@ERROR = 0
BEGIN
SET @countinsert = @countinsert + 1
PRINT 'SSCC: ' + @sscc + ' inséré dans la base.'
UPDATE tPalProd SET nlog = -1 WHERE strSSCC = @sscc
END
ELSE
PRINT 'Problème à l''insertion du SSCC: ' + @sscc + '.'
END
FETCH NEXT FROM C_Export
INTO @sscc
END
CLOSE C_Export
DEALLOCATE C_Export |
Partager