7M2iMSYJ

1USE TestDB;
2GO
3
4IF EXISTS (SELECT * 
5                 FROM INFORMATION_SCHEMA.TABLES 
6				 WHERE TABLE_NAME = 'UKMilitaryUnits')
7				 DROP TABLE UKMilitaryUnits ;
8
9 CREATE TABLE UKMilitaryUnits
10					(
11    					id BIGINT IDENTITY(1,1) NOT NULL PRIMARY KEY,
12    					unit_hid HIERARCHYID NOT NULL,
13    					name NVARCHAR(200) NOT NULL,
14    					[level] AS unit_hid.GetLevel() PERSISTED
15					)
16GO
17
18/*Dropping the procedures if exist*/
19
20
21/* Defining the procedures:*/
22
23/*Adding the root*/
24
25IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'addRoot')
26DROP PROCEDURE addRoot
27GO
28
29CREATE PROCEDURE addRoot
30	@name NVARCHAR(200)
31	AS
32	BEGIN
33		DECLARE @Root HIERARCHYID;
34		SET @Root = HIERARCHYID::GetRoot();
35
36		INSERT INTO UKMilitaryUnits 
37		OUTPUT
38			INSERTED.id,
39			INSERTED.unit_hid,
40			INSERTED.unit_hid.ToString() AS unit_hid_string,
41			INSERTED.name
42		VALUES (@Root, @name);
43		END ;
44GO
45
46EXEC addRoot 'British Armed Forces';
47GO
48
49/*Get All*/
50
51IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getAll')
52DROP PROCEDURE getAll
53GO
54
55CREATE PROCEDURE getAll
56	AS
57	BEGIN
58		SELECT *
59		FROM UKMilitaryUnits
60	END
61GO
62
63EXEC getAll;
64GO
65
66
67/* BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD BAD */
68
69
70
71/*Getting the new child hid, between the parent and his former child. former child can be null. */
72
73/*
74IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'getNewChildHid')
75DROP PROCEDURE getNewChildHid
76GO
77
78CREATE PROC getNewChildHid
79	@parent_id BIGINT,
80	@former_child_id BIGINT,
81	@child_hid HIERARCHYID OUT
82	AS
83	BEGIN
84		DECLARE @parentHid HIERARCHYID, @formerChildHid HIERARCHYID, @max_child_hid HIERARCHYID;
85 
86		SELECT @parentHid = unit_hid FROM UKMilitaryUnits WHERE id = @parent_id;
87		SELECT @formerChildHid = unit_hid FROM UKMilitaryUnits WHERE id = @former_child_id
88
89
90
91
92 
93		--SELECT @max_child_hid = MAX(unit_hid)
94		--FROM UKMilitaryUnits
95		--WHERE unit_hid.GetAncestor(1) = @parentHid;
96 
97		--SET @child_hid = @parentHid.GetDescendant(@max_child_hid, @formerChildHid);
98
99
100		--weird flex but ok
101		--IF @parentHid < @formerChildHid 
102		--SET @child_hid = @parentHid.GetDescendant(@parentHid, @formerChildHid);
103		--ELSE SET @child_hid = @parentHid.GetDescendant(@parentHid, null);
104	END
105GO
106
107
108DECLARE @new_child_hid HIERARCHYID;
109EXEC getNewChildHid 1,3, @new_child_hid OUT;
110EXEC getAll;
111*/
112
113/* Adding the descendant. Descendant will be situated between child_1 and child_2*/
114
115IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'addDescendant')
116DROP PROCEDURE addDescendant
117GO
118
119CREATE PROCEDURE addDescendant
120	@child_1 BIGINT,
121	@child_2 BIGINT,
122	@name NVARCHAR(200)
123	AS
124	BEGIN
125		DECLARE @new_child_hid HIERARCHYID;
126
127		EXEC getNewChildHid @child_1,@child_2, @new_child_hid OUT;
128
129		--SET @new_child = (SELECT unit_hid 
130		--	FROM UKMilitaryUnits 
131		--	WHERE id = 1).GetDescendant(@child_1, @child_2);
132
133		INSERT INTO UKMilitaryUnits
134		OUTPUT
135			INSERTED.id,
136			INSERTED.unit_hid,
137			INSERTED.unit_hid.ToString() AS unit_hid_string,
138			INSERTED.name
139		VALUES (@new_child_hid, @name);
140	END
141GO 
142
143--CREATE PROCEDURE addDescendant
144--	@child_1 HIERARCHYID,
145--	@child_2 HIERARCHYID,
146--	@name NVARCHAR(200)
147--	AS
148--	BEGIN
149--		DECLARE @new_child HIERARCHYID;
150--		SET @new_child = (SELECT unit_hid 
151--			FROM UKMilitaryUnits 
152--			WHERE id = 1).GetDescendant(@child_1, @child_2);
153
154--		INSERT INTO UKMilitaryUnits 
155--		OUTPUT
156--			INSERTED.id,
157--			INSERTED.unit_hid,
158--			INSERTED.unit_hid.ToString() AS unit_hid_string,
159--			INSERTED.name
160--		VALUES (@new_child, @name);
161--	END
162--GO 
163
164/*Adding the first descendant */
165EXEC addDescendant null, null, 'British Army';
166--EXEC addDescendant null, null, 'British Army';
167
168/*Adding other descendants*/
169
170--EXEC addDescendant 
171
172
173
174DECLARE @child_1 HIERARCHYID;
175
176
177DECLARE @parent HIERARCHYID;
178DECLARE @child_2 HIERARCHYID, @child_3 HIERARCHYID;
179 
180SELECT @parent = unit_hid 
181FROM UKMilitaryUnits 
182WHERE id = 1;
183 
184SELECT @child_1 = unit_hid 
185FROM UKMilitaryUnits 
186WHERE id = 2;
187
188SET @child_2 = @parent.GetDescendant(@child_1, null);
189SET @child_3 = @parent.GetDescendant(@child_1, @child_2);
190 
191INSERT INTO UKMilitaryUnits
192OUTPUT
193	INSERTED.id,
194	INSERTED.unit_hid,
195	INSERTED.unit_hid.ToString() AS unit_hid_string,
196	INSERTED.name
197VALUES
198      (@child_2, 'Машков'),
199      (@child_3, 'Сидоров');
200GO