adw loop.sql

/*  SETUP CODE IF REQUIRED
SET NOCOUNT ON
DROP TABLE IF EXISTS dbo.Features;
DROP TABLE dbo.Scenarios
GO

CREATE TABLE dbo.Features
(
	FeatureId		BIGINT NOT NULL,
	FeatureName		VARCHAR(255) NOT NULL,
	ParentId		BIGINT NOT NULL
)
WITH   
	( 
	DISTRIBUTION = HASH ( FeatureId ),  
	HEAP  
	)  
;
GO

insert into Features values(10, 'Feature 1', 1);
insert into Features values(11, 'Feature 2', 10);
insert into Features values(12, 'Feature 3', 11);
insert into Features values(13, 'Feature 4', 2);
insert into Features values(14, 'Feature 5', 13);

insert into Features values(15, 'Feature 6', 3);
insert into Features values(16, 'Feature 7', 15);
insert into Features values(17, 'Feature 8', 16);
insert into Features values(18, 'Feature 9', 17);
insert into Features values(19, 'Feature 10', 18);
insert into Features values(20, 'Feature 11', 19);
insert into Features values(21, 'Feature 12', 12);
GO


CREATE TABLE dbo.Scenarios
(
	ScenarioId		BIGINT NOT NULL,
	ParentId		BIGINT NOT NULL,
	ScenarioTitle	VARCHAR(25) NOT NULL
)
WITH   
	(   
	DISTRIBUTION = HASH ( ParentId ),  
	HEAP   
	)
;  

insert into Scenarios values(1, 0, 'Scenario 1')
insert into Scenarios values(2, 0, 'Scenario 2')
insert into Scenarios values(3, 0, 'Scenario 3')

SELECT *
FROM dbo.Features f

SELECT *
FROM dbo.Scenarios s
*/


-- Loop thru Features
DECLARE @counter INT = 1;

-- Insert first record where no parent exists
IF OBJECT_ID('tempdb..#features') IS NOT NULL DROP TABLE #features;

CREATE TABLE #features
WITH
	(
	DISTRIBUTION = HASH ( FeatureId ),
	LOCATION = USER_DB
	)
AS
WITH cte AS
(
SELECT 1 AS xlevel, p.FeatureId, p.ParentId, p.FeatureName, CAST( p.ParentId AS VARCHAR(255) ) AS PathString, 0 AS PathLength
FROM dbo.Features p
WHERE NOT EXISTS 
	(
	SELECT *
	FROM dbo.Features c
	WHERE p.ParentId = c.FeatureId
	)
)
SELECT *
FROM cte;


SELECT 'before' s, * FROM #features ORDER BY FeatureId;

WHILE EXISTS (
SELECT *
	FROM #features p
		INNER JOIN dbo.features c ON p.FeatureId = c.ParentId
	WHERE p.xlevel = @counter
	)
BEGIN

	-- Insert next level
	INSERT INTO #features ( xlevel, FeatureId, ParentId, FeatureName, PathString, PathLength )
	SELECT @counter + 1 AS xlevel, c.FeatureId, c.ParentId, c.FeatureName, p.PathString + '/' + CAST( c.ParentId AS VARCHAR(255) ) AS PathString, @counter AS PathLength
	FROM #features p
		INNER JOIN dbo.features c ON p.FeatureId = c.ParentId
	WHERE p.xlevel = @counter;
	
	SET @counter += 1;

	-- Loop safety
	IF @counter > 99
	BEGIN 
		RAISERROR( 'Too many loops!', 16, 1 ) 
		BREAK 
	END;

END


SELECT 'after' s, *  FROM #features ORDER BY FeatureId;