WITH common_table_expression (Transact-SQL)

Information at link.

I’ll write this down so I won’t forget it. This common table expression enables you to omit CURSORS and apparently it goes quicker.

This query

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
INSERT #TempTreeStructures (TreeStructureId) VALUES (@RootTreeStructureId)
 
SET @RootRightValue = @RootRightValue - 1
 
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RootLeftValue + 1
 
WHILE (@RightValue < @RootRightValue)
BEGIN
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT @RightValue = RightValue,
@TreeStructureId = TreeStructureId
FROM TreeStructures
WHERE TreeId = @TreeId AND
LeftValue = @RightValue + 1
END
 
INSERT #TempTreeStructures (TreeStructureId) VALUES (@TreeStructureId)
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
tn.Description,
tn.DateChanged ,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId = (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures)
AND tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)
UNION ALL
SELECT ts.TreeStructureId,
ts.LeftValue,
ts.RightValue,
tn.Name,
tn.TYPE,
tn.DATA ,
NULL AS [Description],
NULL AS DateChanged,
tn.UserId
FROM TreeStructures ts
JOIN TreeNodes tn ON ts.TreeStructureId = tn.TreeStructureId
WHERE ts.TreeStructureId IN (SELECT TreeStructureId FROM #TempTreeStructures) AND
ts.TreeStructureId <> (SELECT TOP 1 TreeStructureId FROM #TempTreeStructures) AND
tn.TreeNodeId = (SELECT TOP 1 tn2.TreeNodeId FROM TreeNodes tn2 WHERE tn2.TreeStructureId = ts.TreeStructureId ORDER BY tn2.DateChanged DESC)

could be replaced by this one

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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
;WITH TreeData (TreeStructureId, LeftValue, RightValue, TreeId)
AS (
SELECT TOP 1
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
WHERE TreeStructures.TreeId = @TreeId
AND TreeStructures.LeftValue = @RootLeft + 1
UNION ALL
SELECT
TreeStructures.TreeStructureId
, TreeStructures.LeftValue
, TreeStructures.RightValue
, TreeStructures.TreeId
FROM TreeStructures
INNER JOIN TreeData ON TreeStructures.LeftValue = TreeData.RightValue + 1
WHERE TreeStructures.TreeId = TreeData.TreeId
)
SELECT TreeStructures.TreeStructureId,
TreeStructures.LeftValue,
TreeStructures.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA ,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeStructures
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeStructures.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeStructures.TreeStructureId
ORDER BY DateChanged DESC
)
AND TreeStructures.TreeStructureId = @RootTreeStructureId
UNION ALL
SELECT TreeData.TreeStructureId,
TreeData.LeftValue,
TreeData.RightValue,
TreeNodes.Name,
TreeNodes.TYPE,
TreeNodes.DATA,
TreeNodes.Description,
TreeNodes.DateChanged ,
TreeNodes.UserId
FROM TreeData
JOIN TreeNodes ON TreeNodes.TreeStructureId = TreeData.TreeStructureId
WHERE TreeNodes.TreeNodeId = (
SELECT TOP 1 tn2.TreeNodeId
FROM TreeNodes tn2
WHERE TreeStructureId = TreeData.TreeStructureId
ORDER BY DateChanged DESC
)
ORDER BY LeftValue
;

I’ll have to read some more on it to fully understand its capabilities.

thx, Lieven Cardoen

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>