Code:
...
TRUNCATE TABLE dbo.dummy_AppStruktur1
SELECT @prod_id = MIN(prod_id) FROM dbo.products
WHILE @prod_id Is Not Null
BEGIN
BEGIN TRY
;
WITH struktur_cte AS
(
SELECT
@prod_id AS prod_id,
UPPER(CAST(Right('00' + utilities.dbo.udf_NumToNBase32(st_rad,2,2),2) AS varchar(52)))
AS prod_pos_nr,
@prod_id AS prod_art_id,
1 AS st_level,
st_id,
st_hart_id,
st_rad,
st_art_id,
st_pos_nr,
st_top,
st_kvant,
st_kvant AS st_kvant_product,
Null AS st_kvant_total,
st_kvant_extra,
st_kvant_inst,
st_typ
FROM
ERPMirror.dbo.struktur
WHERE
st_hart_id = @prod_id
UNION ALL
SELECT
@prod_id AS prod_id,
UPPER(CAST(ecte.prod_pos_nr + Right('00' + utilities.dbo.udf_NumToNBase32(e.st_rad,2,2),2) AS varchar(52)))
AS prod_pos_nr,
@prod_id AS prod_art_id,
ecte.st_level+1 AS st_level,
e.st_id,
e.st_hart_id,
e.st_rad,
e.st_art_id,
e.st_pos_nr,
e.st_top,
e.st_kvant,
CAST(ecte.st_kvant_product*e.st_kvant AS numeric(16,6)) AS st_kvant_product,
Null AS st_kvant_total,
e.st_kvant_extra,
e.st_kvant_inst,
e.st_typ
FROM
ERPMirror.dbo.struktur e
INNER JOIN struktur_cte ecte ON ecte.st_art_id = e.st_hart_id
)
INSERT INTO dbo.dummy_AppStruktur1
SELECT
*
FROM
struktur_cte
ORDER BY
prod_pos_nr option(maxrecursion 10000)
END TRY
BEGIN CATCH
INSERT INTO dbo.productFlawedStructur (
prod_id,
art_id,
art_artnr)
SELECT
prod.prod_id,
prod.art_id,
prod.art_artnr
FROM dbo.products prod
WHERE
prod.art_artnr = @art_artnr
AND
prod.prod_id NOT IN (SELECT prod_id FROM dbo.productFlawedStructur)
END CATCH;
SELECT @prod_id = MIN(prod_id)
FROM dbo.products
WHERE prod_id > @prod_id
END
...