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
| -- Déclaration des variables
DECLARE @nouvelle_taille VARCHAR(20)
DECLARE @ancienne_taille VARCHAR(20)
DECLARE @taille_FR VARCHAR(8)
DECLARE @id int
-- Déclaration du curseur
DECLARE curseur_WOEF CURSOR FOR
SELECT [id],[article_UK] as 'ancien_taille',
substring([article_UK],1,9) +
(
SELECT FR
FROM [Intelligent_part_number].[dbo].[INTELLIGENT_PART_NUMBER_style] a, [Intelligent_part_number].[dbo].[INTELLIGENT_PART_NUMBER_size_country] b
where a.id_style = b.id_style and initiale = substring([article_UK],1,2) and UK = rtrim(substring([article_UK],10,5))
) as 'nouvelle_taille',
(
SELECT FR
FROM [Intelligent_part_number].[dbo].[INTELLIGENT_PART_NUMBER_style] a, [Intelligent_part_number].[dbo].[INTELLIGENT_PART_NUMBER_size_country] b
where a.id_style = b.id_style and initiale = substring([article_UK],1,2) and UK = rtrim(substring([article_UK],10,5))
) as 'FR'
FROM [Web_Order_Entry].[dbo].[caddy]
where utilisateur = 'admin' and date_commande = '20151013'
order by id
-- Ouverture curseur
OPEN curseur_WOEF
-- 1er ligne - Association curseur/variable
FETCH NEXT FROM curseur_WOEF INTO @id, @ancienne_taille , @nouvelle_taille, @taille_FR
-- Association curseur/variable
-- Boucle
WHILE @@FETCH_STATUS = 0
BEGIN
IF @nouvelle_taille IS NOT NULL
BEGIN
UPDATE [Web_Order_Entry].[dbo].[caddy]
SET [article_UK] = @nouvelle_taille
WHERE id = @id
END
-- Ligne suivante - Association curseur/variable
FETCH NEXT FROM curseur_WOEF INTO @id, @ancienne_taille , @nouvelle_taille, @taille_FR
END
CLOSE curseur_WOEF
DEALLOCATE curseur_WOEF |
Partager