Tree Data Processing Solution: add, modify, copy, delete, check data integrity, and collect statistics

/* -- Tree data processing solution: sorting, adding, modifying, copying, deleting, checking data integrity, and collecting statistics -- by creation --*/           /* -- Data test environment table name TB. If you modify the table name, modify the table name tb id involved in all data processing as the number (Id field + primary key) PID is the name of the superior number, and other fields can be added later. unless otherwise specified, the added field will not affect the processing result --*/ Create Table Tb (ID int identity (1, 1) not null constraint pk_tb primary key clustered, PID int, name varchar (20) insert into TB select 0 ,' China 'Union all select 0 ,' USA 'Union all select 0 ,' Canada 'Union all select 1 ,' Beijing 'Union all select 1 ,' Shanghai 'Union all select 1 ,' Jiangsu 'Union all select 6 ,' Suzhou 'Union all select 7 ,' Changshu 'Union all select 6 ,' Nanjing 'Union all select 6 ,' Wuxi 'Union all select 2 ,' New York 'Union all select 2 ,' San Francisco 'Go

/* -- Data processing --*//* -- An important function, which is used in many processes --*/-- Custom function -- get the total number of codesCreate Function f_getmergid (@ id int) returns varchar (8000) as begin declare @ Re varchar (8000), @ PID int-- Uniform coding width is required for normal numeric sortingDeclare @ idlen int, @ idheader varchar (20) Select @ idlen = max (LEN (ID), @ idheader = space (@ idlen) from TB-- Get the total number of codesSet @ Re = right (@ idheader + Cast (@ ID as varchar), @ idlen) select @ pid = PID from TB where id = @ ID while @ rowcount> 0 select @ Re = right (@ idheader + Cast (@ PID as varchar), @ idlen) +','+ @ Re, @ pid = PID from TB where id = @ PID return (@ Re) end go
 

 

 /* -- Sort data display --*/      -- Hierarchical display -- horizontal, first level, then Level 2... Select * from TB order by PID -- Hierarchical display -- vertical Select * from TB order by DBO. f_getmergid (ID) Go /* -- Data statistics --*/       -- Hierarchical statistics: number of detailed regions in each region Select *, number of detailed regions = (select count (*) from TB where DBO. f_getmergid (ID) Like DBO. f_getmergid (A. ID) +' , % ') From TB a order by DBO. f_getmergid (ID) Go /* -- Data addition, data addition modification, and modification (including modifying the category) are not skillful. You only need to check whether the upper-level owner exists. this can be simply solved using the following statement: If exists (select 1 from TB where id = @ ID) print 'has 'else print' Doesn't exist '--*/           /* -- Delete data --*/ Create proc p_delete @ ID int, -- ID to be deleted @ Deletechild bit = 0 -- Whether to delete sub-1. Delete sub-, 0. If @ ID has sub-, the deletion fails. As if @ deletechild = 1 Delete from TB where DBO. f_getmergid (ID) Like DBO. f_getmergid (@ ID) +' % 'Else if exists (select 1 from TB where pid = @ ID) goto lberr else Delete from TB where id = @ ID return lberr: raiserror (' This node has subnodes and cannot be deleted. ', 16, 1) Go -- Call example    -- Delete data from 'America'    -- Exec p_delete 2 -- does not contain sub-accounts. deletion may fail because there are sub-accounts in the United States. Exec p_delete 2, 1-- Contains sub-records, which will delete the United States and all data Go

 

 /* -- Data Integrity check --*/       -- Custom function -- checks whether a code is referenced cyclically. Create Function f_chkid (@ id int) returns bit -- Loop. 1 is returned. Otherwise, 0 is returned. As begin declare @ Re bit, @ PID int set @ Re = 0 -- Detection Select @ pid = PID from TB where id = @ ID while @ rowcount> 0 begin if @ pid = @ ID begin set @ Re = 1 goto lberr end select @ pid = PID from TB where id = @ PID end lberr: return (@ Re) end go -- Displays the data in the table that does not comply with the specifications. Select * from tb a where not exists (select 1 from TB where id = A. PID) or DBO. f_chkid (ID) = 1 go /* -- Data Replication if the table contains custom fields, you need to modify the stored procedure to have a nested value of no more than 32 layers .--*/           -- Create a replication stored procedure -- copy the child node under a specified node to another node Create proc p_copy @ s_id int, -- Copy all subitems under the item @ D_id int, -- Copy to this item @ New_id int -- Add the start number of the add item As declare @ NID int, @ OID int, @ name varchar (20) Select ID, name into # temp from TB where pid = @ s_id and ID <@ new_id while exists (select 1 from # temp) Begin select @ OID = ID, @ name = Name from # temp insert into TB values (@ d_id, @ name) set @ nid = @ identity exec p_copy @ OID, @ NID, @ new_id Delete from # temp where id = @ OID end go -- Create a batchcopy stored procedure -- copy a specified node and all its subnodes and generate a new node. Create proc p_copystr @ s_id varchar (8000) -- List of items to be replicated, separated by commas As declare @ NID int, @ OID int, @ name varchar (20) set @ s_id =' , '+ @ S_id +' , 'Select ID, name into # temp from TB where charindex (', '+ Cast (ID as varchar) +' , ', @ S_id)> 0 while exists (select 1 from # temp) Begin select @ OID = ID, @ name = Name from # temp insert into TB values (@ OID, @ name) set @ nid = @ identity exec p_copy @ OID, @ NID, @ NID Delete from # temp where id = @ OID end go -- Test Exec p_copystr' 5, 6 ' -- Display the processing result Select * from TB order by DBO. f_getmergid (ID) Go -- Delete the data test environment Drop table TB drop function f_getmergid, f_chkid drop proc p_delete, p_copystr, p_copy

 /* -- Data Integrity check -- */ 
  -- custom function -- checks whether a code is referenced cyclically  
 Create Function f_chkid (@ id int) 
 Returns bit  -- loop, return 1, otherwise, 0  
 as 
 begin 
 declare @ Re bit is returned, @ PID int 
 
 
 set @ Re = 0 
 
  -- detection  
 select @ pid = PID from TB where id = @ ID 
 while @ rowcount> 0 
 begin 
 If @ pid = @ ID 
 begin 
 set @ Re = 1 
 goto lberr 
 end