This Stored Procedure deletes the children of a node in a left/right tree (Preorder Tree Traversal). The two tables are what you need to understand the Stored Procedure. This SP is allmost identical to the sp_deletetreestructure.

treestructures Preorder Tree Traversal (left/right tree) Deleting children of a Node MSSQL2005

trees Preorder Tree Traversal (left/right tree) Deleting children of a Node MSSQL2005

deletetreestructurechildren :

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
59
60
61
62
63
64
65
66
67
68

/******
Object:  StoredProcedure [dbo].[sp_DeleteTreeStructureChildren]
Script Date: 08/17/2008 19:12:16
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_DeleteTreeStructureChildren]
(
@TreeStructureId INT -- Id TreeStructure which children should be deleted
)
AS
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
 
    DECLARE @TranName NVARCHAR(MAX)
    SELECT @TranName = 'DeleteTreeStructureChildren';
    BEGIN TRANSACTION @TranName
 
    BEGIN TRY
 
        DECLARE @RightValue INT
        DECLARE @LeftValue INT
        DECLARE @Width INT
        DECLARE @TreeId INT
 
        SELECT	@RightValue = (RightValue - 1),
        		@LeftValue = (LeftValue + 1),
        		@Width = (RightValue - LeftValue) - 1,
        		@TreeId = TreeId
        FROM	TreeStructures
        WHERE	(TreeStructureId = @TreeStructureId)
 
        DELETE
        FROM	TreeStructures
        WHERE	(LeftValue BETWEEN @LeftValue AND @RightValue)
        AND 	TreeId = @TreeId
 
        UPDATE	TreeStructures
        SET 	RightValue = RightValue - @Width
        WHERE	RightValue > @RightValue
        AND 	TreeId = @TreeId
 
        UPDATE	TreeStructures
        SET 	LeftValue = LeftValue - @Width
        WHERE	LeftValue > @RightValue
        AND 	TreeId = @TreeId
 
    COMMIT TRANSACTION @TranName
 
    END TRY
 
    BEGIN CATCH
 
    /*
    If an error occured we Roll Back the transaction
    and raise an error that VS can catch
    */
 
    ROLLBACK TRANSACTION @TranName
    RAISERROR 50001 'Error in sp_DeleteTreeStructureChildren.
    Rollback has been executed.'
 
    /**********************************************************/
 
    END CATCH
END
pixel Preorder Tree Traversal (left/right tree) Deleting children of a Node MSSQL2005
No TweetBacks yet. (Be the first to Tweet this post)