OnTopic-Library/OnTopic.Data.Sql.Database/Utilities/Stored Procedures/CompressHierarchy.sql at 2df59ad9e527839557aef432a9843a4a86046715 · OnTopicCMS/OnTopic-Library · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
--------------------------------------------------------------------------------------------------------------------------------
-- COMPRESS HIERARCHY
--------------------------------------------------------------------------------------------------------------------------------
-- Remove gaps within nested set model created when non-leaf nodes are deleted
--------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [Utilities].[CompressTopics]
AS

SET NOCOUNT ON;

--------------------------------------------------------------------------------------------------------------------------------
-- MIND THE GAP!
--------------------------------------------------------------------------------------------------------------------------------
UPDATE	Topics
SET	RangeLeft = (
  SELECT	COUNT(*)
  FROM	LeftRightRange
  WHERE	seq <= RangeLeft
),
	RangeRight = (
  SELECT	COUNT(*)
  FROM	LeftRightRange
  WHERE	seq <= ISNULL(RangeRight, 0)
);