Содержимое функции такое:
Код CREATE FUNCTION [dbo].[f_ShopScriptExport3] (
@p_iGRID int = null
)
RETURNS @tRetTable table (
RecID int,
SORT int, --Порядок сортировки
G_ID int, --Внутренний код (артикул)
G_NAME varchar(250), --Наименование
G_DESC varchar(8000), --Описание
G_SHORT_DESC varchar(8000), --Краткое описание
G_PRICE money, --Цена
G_OLD_PRICE money, --Старая цена
G_META1 varchar(8000), --Тэг META keywords
G_META2 varchar(8000), --Тэг META description
G_PHOTO1 varchar(200), --Фотография
G_PHOTO2 varchar(200), --Фотография
G_PHOTO3 varchar(200), --Фотография
G_COLOUR varchar(200), --Цвет
G_ADD_DESC varchar(8000), --Дополнительная информация (2)
G_VENDOR varchar(8000), --Производитель
GR_ID int
)
AS
BEGIN
DECLARE @l_iRecID int, @l_iGRID int, @l_sGRNAME varchar(200), @l_iSort int;
SET @l_iSort = 1;
SET @l_iRecID = 1;
DECLARE cExportGroups CURSOR LOCAL FORWARD_ONLY LOCAL FOR
SELECT gr.GR_ID, gr.GR_NAME from TBL_GROUPS gr
WHERE gr.GR_PARENT_ID is null
AND
(
@p_iGRID is null
OR
(
not @p_iGRID is null
and gr.GR_ID = @p_iGRID
)
)
AND GR_ID<>209
FOR READ ONLY;
OPEN cExportGroups;
FETCH NEXT FROM cExportGroups INTO @l_iGRID, @l_sGRNAME;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
-- INSERT INTO @tRetTable (RecID, SORT, GR_ID, G_NAME)
-- VALUES (@l_iRecID, @l_iSort, @l_iGRID, @l_sGRNAME);
-- IF (SELECT COUNT (*) FROM TBL_GROUPS gr WHERE gr.GR_PARENT_ID = @l_iGRID) >0
-- BEGIN
INSERT INTO @tRetTable
SELECT *
FROM dbo.f_ShopScriptExportGoods3(@l_iRecID, @l_iGRID, 1);
SELECT @l_iRecID = MAX(RecID) FROM @tRetTable;
-- END
SET @l_iSort = @l_iSort + 1;
SET @l_iRecID = @l_iRecID + 1;
FETCH NEXT FROM cExportGroups INTO @l_iGRID, @l_sGRNAME;
END
CLOSE cExportGroups;
DEALLOCATE cExportGroups;
RETURN;
END
|
