Asp. Non-recursive implementation of net infinite-level classification (stored procedure version)

More or less have done a tree directory, product classification, such as level two or level three menu, if you encounter more levels of classification, the general use of recursion. Using recursion in a program more or less adds some performance overhead.

I used asp.net to implement a non recursive infinite class directory in the program, but considering the transplant is not strong, changed to a stored procedure, sent out to the common research, so far, the test process has not found the problem, in addition, the code has not been optimized.

Usually, we read the directory more, so, in the following implementation, read we only need a SELECT statement is enough, do not use recursion, theoretically infinite class ~.
===================================================
Table structure:
Table Name: Tb_column
Table structure (all fields are non-null):
column_id Int PRIMARY KEY (note: Non-identity)
column_name nvarchar (50) category name
parent_id int parent category ID (default value 0)
Column_path nvarchar (1000) Classification path
column_depth int classification Depth (default value 0)
Column_order int sort (default value 0)
Column_intro nvarchar (1000) Classification description

================================================
Stored procedure one: new category

CREATE PROCEDURE Sp_column_insert
(
@Parent_ID int,
@Column_Name nvarchar (50),
@Column_Intro nvarchar (1000)
)
As
Declare @Err as int
Set @Err =0

Begin Tran
--Get column IDs from existing records
Declare @Column_ID as int
Declare @Column_Depth as int
Select @Column_ID = Max (column_id) from Tb_column
IF @Column_ID is not Null
Set @Column_ID = @Column_ID +1
Else
Set @Column_ID = 1

--to determine whether it is the top column, set its Column_path and Column_order
Declare @Column_Path as nvarchar (1000)
Declare @Column_Order as int
IF @Parent_ID = 0
Begin
Set @Column_Path =ltrim (STR (@Column_ID))
Select @Column_Order = Max (column_order) from Tb_column
IF @Column_Order is not Null
Set @Column_Order = @Column_Order + 1
Else-If there is no query to the record, this is the first record
Set @Column_Order = 1

--depth
Set @Column_Depth = 1
End
Else
Begin
--Gets the path and depth of the parent node
Select @Column_Path = column_path, @Column_Depth = column_depth from Tb_column Where

Column_id= @Parent_ID
IF @Column_Path is Null
Begin
Set @Err = 1
Goto theend
End

--Gets the maximum ordinal number under the half node
Select @Column_Order = Max (column_order) from Tb_piccolumn Where column_path like

' + @Column_Path + ' |% ' Or column_id = @Parent_ID
If @Column_Order is isn't Null--if the ordinal exists, then all numbers after that ordinal are added to 1
Begin
--Update the ordinal number of all nodes after the current node is inserted
Update tb_column Set column_order = column_order +1 Where column_order

> @Column_Order
--the maximum ordinal number plus 1 in the half, forming its own serial number
Set @Column_Order = @Column_Order + 1
End
Else
Begin
Set @Err =1
Goto theend
End

--The path of the parent node plus its own ID number to form its own path
Set @Column_Path = @Column_Path + ' | ' + Ltrim (Str (@Column_ID))

--depth
Set @Column_Depth = @Column_Depth +1

End

Insert into Tb_column (Column_id,column_name,parent_id,column_path,column_depth,column_order,column_intro)

Values (@Column_ID, @Column_Name, @Parent_ID, @Column_Path, @Column_Depth, @Column_Order, @Column_Intro)

IF @ @Error <>0
Begin
Set @Err =1
Goto theend
End

--Order of the records after the current record is updated
--update tb_column Set column_order = column_order+1 Where column_order > @Column_Order

TheEND:
IF @Err =0
Begin
Commit Tran
Return @Column_ID
End
Else
Begin
Rollback Tran
return 0
End
Go

===================================================
Stored procedure two: deleting categories
CREATE PROCEDURE Sp_column_delete
(
@Column_ID int
)
As
Declare @Err as int
Set @Err = 0
Begin Tran
--First query if there are child nodes under the node
Select column_id from tb_column Where parent_id = @Column_ID
IF @ @RowCount <>0
Begin
Set @Err = 1
Goto theend
End

--Gets the column_order of the node and the order in which the other records are sorted after deletion
Declare @Column_Order as int
Select @Column_Order = Column_order from tb_column Where column_id = @Column_ID
IF @Column_Order is NUll
Begin
Set @Err =2
Goto theend
End

--Update Column_order for other records
Update tb_column Set column_order = column_order-1 Where column_order > @Column_Order
IF @ @Error <>0
Begin
Set @Err =3
Goto theend
End

--delete operation
Delete from Tb_column Where column_id= @Column_ID
IF @ @Error <>0
Begin
Set @Err =4
Goto theend
End

--Update other records column_id
--update tb_column Set column_id= column_id-1 Where column_id > @Column_ID
--if @ @Error & Lt;>0
--    Begin
--      Set @Err =5
--       Goto theend
--    End

TheEND:
IF @Err = 0
Begin
Commit Tran
Return 0--delete successful
End
Else
Begin
IF @Err =1
Begin
Rollback Tran
Return 1--there are child nodes
End
Else
Begin
Rollback Tran
Return 2--Unknown error
End
End
Go
=============================================
Stored procedure three: Edit category
CREATE PROCEDURE Sp_column_update
(
@Column_ID int,
@Parent_ID int,
@Column_Name nvarchar (50),
@Column_Intro nvarchar (1000)
)
As
Declare @Err as int
Set @Err =0

Begin Tran

--Get the modified before: Parent_id,column_depth,column_order
Declare @oParent_ID as int
Declare @oColumn_Depth as int
Declare @oColumn_Order as int
Declare @oColumn_Path as nvarchar (1000)

Select @oParent_ID = parent_id, @oColumn_Depth = column_depth, @oColumn_Order = column_order, @oColumn_Path = Column_path F Rom tb_column Where column_id = @Column_ID
IF @oParent_ID is Null
Begin
Set @Err = 1
Goto theend
End

--If the parent ID has not changed, then directly modify the column name and column introduction
IF @oParent_ID = @Parent_ID
Begin
Update tb_column Set column_name = @Column_Name, Column_intro = @Column_Intro Where column_id = @Column_ID
IF @ @Error <> 0
Set @Err = 2
Goto theend
End

Declare @nColumn_Path as nvarchar (1000)
Declare @nColumn_Depth as int
Declare @nColumn_Order as int

--Gets the number of nodes that the current node contains as a parent node [includes itself] Note: If the return "1" description is a single node
Declare @theCount as int
Select @theCount = Count (column_id) from Tb_column Where column_id= @Column_ID Or column_path like ' + @oColumn_Path + ' |% '
IF @theCount is Null
Begin
Set @Err = 3
Goto theend
End

If @Parent_ID = 0--If it is set to the top-level node, set the node to the last top-level node
Begin
--print ' Set as top column '
Set @nColumn_Path = Ltrim (Str (@Column_ID))
Set @nColumn_Depth =1

Select @nColumn_Order = Max (column_order) from Tb_column
IF @nColumn_Order is NULL
Begin
Set @Err = 4
Goto theend
End

Set @nColumn_Order = @nColumn_Order-@theCount + 1

--Update three part 1 node itself 2 all child nodes 2 The order of the following records before the tree changes
--print ' Update this column before the position of all columns [excluding the sub columns under this column]: Column_order '
Update tb_column Set column_order = column_order-@theCount Where (Column_order > @oColumn_Order) and (Column_path not Li Ke ' + @oColumn_Path + ' |% ')
IF @ @Error <> 0
Begin
Set @Err = 7
Goto theend
End

--print ' Update this column: Parent_id,column_path,column_depth,column_order,column_name,column_intro '
Print ' order: ' + Ltrim (STR (@nColumn_Order))
Update tb_column Set parent_id= @Parent_ID, Column_path = @nColumn_Path, column_depth = @ Ncolumn_depth,column_order = @nColumn_Order, COLUMN_NAME = @Column_Name, Column_intro = @Column_Intro Where column_id = @c olumn_id
IF @ @Error <> 0
    Begin
    Set @Err = 5
    Goto theend
    End

--print ' Updates all sub columns under this column: Column_path,column_depth,column_order '
              Update tb_column Set column_path = Replace (Column_path, @oColumn_Path, @nColumn_Path), Column_depth = column_depth + (@nColumn_Depth-@oColumn_Depth), Column_order = column_order+ (@nColumn_Order-@oColumn_ Order) Where Column_path like ' + @oColumn_Path + ' |% '
IF @ @Error <> 0
    Begin
     Set @Err = 6
    Goto theend
    End

End
Else
Begin
--Get information about the future parent node and set the correlation value of this node
Select @nColumn_Depth = column_depth, @nColumn_Path = Column_path from tb_column Where column_id = @Parent_ID
IF @nColumn_Depth is null Or @nColumn_Path is null
Begin
Set @Err = 8
Goto theend
End
Set @nColumn_Depth = @nColumn_Depth +1
Select @nColumn_Order =max (column_order) from tb_column Where column_id = @Paren

t_id Or column_path like ' + @nColumn_Path + ' |% '
IF @nColumn_Order is NULL
Begin
Set @Err = 9
Goto theend
End

Set @nColumn_Path = @nColumn_Path + ' | ' + Ltrim (STR (@Column_ID))

If @nColumn_Order = @oColumn_Order +1--If the new parent node is the nearest sibling at the top of the previous position, the order of all nodes does not change
Begin
Update tb_column Set parent_id= @Parent_ID, Column_path = @nColumn_Path, column_depth = @nColumn_Depth, COLUMN_NAME = @ Column_name,column_intro = @Column_Intro Where column_id = @Column_ID
IF @ @Error <> 0
Begin
Set @Err = 10
Goto theend
End
End

Set @nColumn_Order = @nColumn_Order + 1

--Update three Part 1 The order of the following (or previous) records before the change the tree 1 node itself 3 all child nodes
--it is divided into moving up or like moving down
--print ' Update this column before the position of all columns [or after this column position] [does not include the Sub column under this column]: Column_order '
IF @nColumn_Order < @oColumn_Order
Begin
Update tb_column Set column_order = column_order+ @theCount Where column_order< @oColumn_Order and Column_order >=@ Ncolumn_order and (Column_path not like ' + @oColumn_Path + ' |% ') and column_id<> @Column_ID
IF @ @Error <> 0
Begin
Set @Err = 12
Goto theend
End
End
Else
Begin
Update tb_column Set column_order = column_order-@theCount Where column_order > @oColumn_Order and column_order<@ Ncolumn_order and (Column_path not like ' + @oColumn_Path + ' |% ') and column_id<> @Column_ID
IF @ @Error <> 0
Begin
Set @Err = 13
Goto theend
End
End

--print ' Update this column: Parent_id,column_path,column_depth,column_order,column_name,column_intro '
Print ' order: ' + Ltrim (STR (@nColumn_Order))
IF @nColumn_Order > @oColumn_Order
Set @nColumn_Order = @nColumn_Order-@theCount
Update tb_column Set parent_id= @Parent_ID, Column_path = @nColumn_Path, column_depth = @nColumn_Depth, Column_order = @nColumn_Order, COLUMN_NAME = @Column_Name, Column_intro = @Column_Intro Where column_id = @Column_ID
IF @ @Error <&G T 0
    Begin
    Set @Err = ten
    Goto theend
     End

--print ' Updates all sub columns under this column: Column_paht,column_depth,column_order '
              Update tb_column Set column_path = Replace (Column_path, @oColumn_Path, @nColumn_Path), Column_depth = column_depth + (@nColumn_Depth-@oColumn_Depth), Column_order = column_order+ (@nColumn_Order-@oColumn_ Order) Where Column_path like ' + @oColumn_Path + ' |% '
IF @ @Error <> 0
    Begin
     Set @Err = one
    Goto theend
    end
End

TheEND:
If @Err <>0-if there are errors, return the error number
   Begin
   Rollback Tran
   return @Err
   end
else    --returns 0
   Begin
If there is no error:    Commit Tran
   return 0
   end
Go
=========================================
Stored procedure four: Display category (just a SELECT statement)
Category list:
CREATE PROCEDURE sp_column_list
as
SELECT column_id, COLUMN_NAME, Parent _id, Column_path, column_depth,
      column_order, Column_intro
from Tb_column
ORDER by Column_order
Go