Infinite classification (non-recursive algorithm/Stored Procedure edition/GUID primary key) complete database example _ (2) Insert record

-- ===================================================== =
-- Author: <Yang junming, jimmy.yang@cntvs.com>
-- Description: <insert records for unlimited categories>
-- Return: 0 is returned for success, and 1 is returned for duplicate names.
-- ===================================================== =
Create procedure [DBO]. [up_class_insertex]
@ Newid uniqueidentifier, -- New Record ID
@ Classtype nvarchar (50), -- type (for example, product, news, and Region)
@ Parentid uniqueidentifier, -- parent class ID
@ Classname nvarchar (50), -- category name
@ Classreadme nvarchar (200) -- Category Description
As
Begin
Set nocount on;
Declare @ rootid int; -- root ID (root ID of top-level classification)
Declare @ parentname nvarchar (500); -- parent class name
Declare @ depth int; -- parent class depth
Declare @ maxorders int; -- maximum same level Order Number
Declare @ parentidstr nvarchar (500); -- full path of the parent class ID
Declare @ parentnamestr nvarchar (500); -- full path of the parent class name
 
-- For top-level classes
If @ parentid = '1970-0000-0000-0000-000000000000'
Begin
If not exists (select f_id from t_class where f_classname = @ classname and f_type = @ classtype and f_parentid = '2017-0000-0000-0000-00000000000000 ')
Begin
Set @ rootid = 0
If exists (select f_id from t_class where f_type = @ classtype and f_parentid = '2017-0000-0000-0000000000000000 ')
Begin
Select @ rootid = max (f_rootid) from t_class where f_type = @ classtype and f_parentid = '2017-0000-0000-0000-000000000000'; -- obtain the maximum rootid of the current top-level category
End

Insert into t_class (
F_id,
F_type,
F_parentid,
F_classname,
F_readme,
F_parentidstr,
F_parentnamestr,
F_rootid,
F_orders)
Values (
@ Newid,
@ Classtype,
'2017-0000-0000-0000-000000000000 ',
@ Classname,
@ Classreadme,
'2017-0000-0000-0000-000000000000 ',
'2017-0000-0000-0000-000000000000 ',
@ Rootid + 1,
0)
Return 0; -- the top-level category is successfully inserted.
End
Else
Return 1; -- the top-level category already exists.
End

-- If it is not a top-level class
If @ parentid <> '2017-0000-0000-0000-000000000000'
Begin
-- Check validity of the parent class ID
If exists (select f_id from t_class where f_id = @ parentid)
Begin
-- Check whether the node already exists
If not exists (select f_id from t_class where f_classname = @ classname and f_type = @ classtype and f_parentid = @ parentid)
Begin
Select @ rootid = f_rootid, @ parentname = f_classname, @ depth = f_depth, @ parentidstr = f_parentidstr, @ parentnamestr = f_parentnamestr from t_class where f_id = @ parentid;
Set @ maxorders = 0
-- If the parent class has no subclass
If not exists (select f_id from t_class where f_parentidstr like '%' + convert (nvarchar (50), @ parentid) + '% ')
Select @ maxorders = f_orders from t_class where f_id = @ parentid
Else
Select @ maxorders = max (f_orders) from t_class where f_parentidstr like '%' + convert (nvarchar (50), @ parentid) + '%'

Insert into t_class (
F_id,
F_type,
F_parentid,
F_classname,
F_readme,
F_parentidstr,
F_parentnamestr,
F_orders,
F_depth,
F_rootid
)
Values (
@ Newid,
@ Classtype,
@ Parentid,
@ Classname,
@ Classreadme,
@ Parentidstr + ',' + convert (nvarchar (50), @ parentid ),
@ Parentnamestr + ',' + @ parentname,
@ Maxorders + 1,
@ Depth + 1,
@ Rootid
)
 
Update t_class set f_orders = f_orders + 1 where (f_orders> @ maxorders) and (f_rootid = @ rootid) and f_id <> @ newid
 

Return 0;
End
Else
Return 1;
End
End
End