SET NOCOUNT ON
DECLARE @ProductId uniqueidentifier
SET @ProductId = '75a42220-ef03-42ec-a8d7-8f212654a646'
CREATE TABLE #TempProducts
(
IndexId int IDENTITY (0, 1) NOT NULL,
ProductId uniqueidentifier,
Quantity int,
TotalQuantity int,
Depth int,
RankLine nvarchar(MAX)
)
DECLARE @Depth int
DECLARE @IndexId int
SET @Depth = 0
SET @IndexId = -1
INSERT #TempProducts(ProductId, Quantity, TotalQuantity, Depth, RankLine)
VALUES (@ProductId, 1, 1, @Depth, CAST(@ProductId AS nvarchar(36)))
A:
SET @IndexId = @IndexId + 1
IF (@IndexId <= SCOPE_IDENTITY())
BEGIN
SET @Depth = @Depth + 1
INSERT #TempProducts (ProductId, Quantity, TotalQuantity, Depth, RankLine)
SELECT c.ChildProductId, c.Quantity, t.TotalQuantity * c.Quantity, @Depth, t.RankLine + STR(c.Rank) + CAST(c.ChildProductId AS nvarchar(36))
FROM dbo.mappor_ChildProducts c INNER JOIN
#TempProducts t ON c.ProductId = t.ProductId AND t.Depth = @Depth - 1
GOTO A
END
SELECT *
FROM #TempProducts
ORDER BY t.RankLine
