support
Support
Количество сообщений
1199
Зарегистрирован:
11-04-2008, 19:11:57
|
Пользовательский SQL-запрос для копирования преобразованного артикула из справочника товаров (с удаленными некоторыми символами) в характеристику с кодом (ID) равным 8543 будет выглядеть так:
Код
DECLARE @G_ID int, @P_ID int, @GP_ID int, @PROP_VALUE varchar(150);
SET @P_ID = 8543;
DECLARE cGoods CURSOR LOCAL READ_ONLY FOR
SELECT G_ID, G_SID, (SELECT TOP 1 GP_ID FROM TBL_GOODS_PROPS WHERE GP_P_ID = @P_ID AND GP_G_ID = G_ID)
FROM TBL_GOODS
WHERE NOT G_SID IS NULL
AND G_SID <> ''
AND NOT EXISTS (SELECT 1 FROM TBL_GOODS_PROPS WHERE GP_P_ID = @P_ID AND GP_G_ID = G_ID AND NOT GP_VALUE IS NULL AND GP_VALUE <> '');
OPEN cGoods;
FETCH NEXT FROM cGoods INTO @G_ID, @PROP_VALUE, @GP_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @PROP_VALUE = REPLACE(@PROP_VALUE, ' ', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '-', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '.', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '(', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, ')', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '/', '');
IF @GP_ID > 0
BEGIN
UPDATE TBL_GOODS_PROPS
SET GP_VALUE = @PROP_VALUE
WHERE GP_ID = @GP_ID;
END
ELSE
BEGIN
INSERT INTO TBL_GOODS_PROPS (GP_VALUE, GP_P_ID, GP_G_ID)
VALUES (@PROP_VALUE, @P_ID, @G_ID);
END;
FETCH NEXT FROM cGoods INTO @G_ID, @PROP_VALUE, @GP_ID;
END;
CLOSE cGoods;
DEALLOCATE cGoods;
Для аналогичных манипуляций с колонкой бренда и записью в характеристику с кодом (ID) равным 8547:
Код
DECLARE @G_ID int, @P_ID int, @GP_ID int, @PROP_VALUE varchar(150);
SET @P_ID = 8547;
DECLARE cGoods CURSOR LOCAL READ_ONLY FOR
SELECT G_ID, B_NAME, (SELECT TOP 1 GP_ID FROM TBL_GOODS_PROPS WHERE GP_P_ID = @P_ID AND GP_G_ID = G_ID)
FROM TBL_GOODS, TBL_BRANDS
WHERE G_B_ID = B_ID
AND NOT EXISTS (SELECT 1 FROM TBL_GOODS_PROPS WHERE GP_P_ID = @P_ID AND GP_G_ID = G_ID AND NOT GP_VALUE IS NULL AND GP_VALUE <> '');
OPEN cGoods;
FETCH NEXT FROM cGoods INTO @G_ID, @PROP_VALUE, @GP_ID;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
SET @PROP_VALUE = REPLACE(@PROP_VALUE, ' ', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '-', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '.', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '(', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, ')', '');
SET @PROP_VALUE = REPLACE(@PROP_VALUE, '/', '');
IF @GP_ID > 0
BEGIN
UPDATE TBL_GOODS_PROPS
SET GP_VALUE = @PROP_VALUE
WHERE GP_ID = @GP_ID;
END
ELSE
BEGIN
INSERT INTO TBL_GOODS_PROPS (GP_VALUE, GP_P_ID, GP_G_ID)
VALUES (@PROP_VALUE, @P_ID, @G_ID);
END;
FETCH NEXT FROM cGoods INTO @G_ID, @PROP_VALUE, @GP_ID;
END;
CLOSE cGoods;
DEALLOCATE cGoods;
|
