/ * - Table structure description and data environment:
Table name TB, if the table name is modified, the corresponding modification of the table name TB ID involved in all data processing is the number (Identification field primary key), the PID is the upper level number, name is the name, and the following can add other fields. Where is not Special labeling places, do not affect processing results
- Zou Jian 2003.12 (Please keep this information) - * /
- Test Data Create Table TB (ID Int Id Id Int Intity (1) Not Null Constraint PK_TB Primary Key Clustered, Pid Int, Name Varchar (20)) Insert Tb SELECT 0, 'China' Union All Select 0, 'US' 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 Slect 6, 'Nanjing' Union All Select 6, 'Wuxi' Union All Select 2, 'New York' Union All Slect 2, 'San Francisco' Go
--1. Custom Function - Get Code Cumulative Create Function F_GetMergid (@ID INT) Returns Varchar (8000) Asbegin Declare @re varchar (8000), @ PID INT
- For the normality, you need unified encoding width declare @Idlen int, @ idheader varchar (20) select @ idlen = max (len (id)), @ idheader = space (@Idlen) from TB
- Get encoding accumulated set @ re = right (@id as varchar), @ idlen) select @ PID = PID from TB where id = @ id while @@ Right> 0 Select @ RE = Right (@ IDHEADER CAST (@PID as varchar), @ idlen) ',' @ RE, @ PID = PID from TB where id = @ pid return (@RE) Endgo
--2. Custom Function - Detects a coded departure, whether it is cycled to reference the Create Function F_CHKID (@ID INT) Returns bit - loop, return 1, otherwise return 0asbegin declare @RE bit, @ pid set @re = 0
- Detect 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) Endgo
/ * - Data replication
If the table contains a custom field, there is a need to modify the memory process without more than 32 layers. - * / - 3. Copy the subjunction point in the specified node to the other node CREATE PROC P_COPY @ S_ID INT, - Copy all child @d_id int, - copy to this @new_id int - Add Item No. ASDECLARE @nid int, @ Oid int, @ name varchar (20) SELECT ID, Name Into #Temp from Tb Where Pid = @ S_ID and ID <@New_IDWHILE 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 = @ Oidendgo
--4. Batch replication stored procedure - copy the specified node and all sub-nodes below, and generate new nodes create proc p_copystr @ s_id varchar (8000) - To copy the list, separated as Declare with comma @nid int, @ name var, @ Name var, @S_ID = ',' @ S_ID ',' SELECT ID, Name Into #temp from Tbwhere Charindex (',' Cast (ID as varchar) ' , ', @S_ID)> 0WHILE 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 = @ Oidendgo
--5. Added sub-ID list of specified ID Create function f_getchildid (@ID int) returns @re table (id int) asbegin insert @re select id from tb where pid = @ ID while @@ rowcount> 0 insert INTO @ Re select a.id from tb a inner join @re b on a.pid = B.ID where A.id Not in (Select ID from @re) ReturnendGo
--6. Get the parent ID list of the specified ID Create function f_getparentId (@ID int) returns @re table (id int) asbegin declare @PID INT SELECT @ PID = PID from Tb Where id = @ ID while @PID <> 0 Begin Insert Into @re value (@PID) SELECT @ PID = PID from Tb Where ID = @ Pid End ReturnendGo - 7. Delete the designated node
Create Proc P_Delete @ id @ deleted ID @ deletechild bit = 0 - Whether to delete the sub-1. Delete sub, 0. If @ID has a child, delete failed .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 (has a sub-node under this node, can't delete ', 16, 1) GO
--8. Get encoding cumulative and encoding level table, this is a full table, mainly should be processed in full table:
CREATE FUNCTION F_GETBMMERG ()
Returns @reat Table (ID INT, IDmerg varchar (8000), Level Int
AS
Begin declare @Idlen int, @ IDHEADER VARCHAR (20), @LEVEL INT SELECT @ idlen = max (id)), @ idheader = space (@Idlen) from tb set @ Level = 1 Insert Into @re select id, Right (@ idheader cast (id as varchar), @ idlen), @ level from tb where pid = 0 while @@ rowcount> 0 begin set @ level = @ Level 1 INSERT INTO @RET B.ID, A. IDMERG ',' Right (@ IDHEADER CAST (B.ID As Varchar), @ idlen), @ level from @re a inner join tb b on a.id = b.pid where a.Level=@level-1 Endreturnendgo
--application:
/ * - Data Show Sort - * / - Hierarchical Display - Landscape, First First, Second Level ... Select * from Tb Order By PID
- Hierarchical display - portrait Select * from Tb Order by dbo.f_getergid (id) Go
/ * - Data Statistics - * / - Hierarchical Statistics, Dianting Areas under Each Region Select *, count number = (Select Count (*) from Tb Where dbo.f_getmerge (ID) Like dbo.F_getMergID A.ID) ',%') from TB A ORDER BY DBO.F_GETMERGID (ID) GO / * - Data New, Modify Data New, Modification (including the category of modification), only need to check Whether the above level is existing. This can be solved simply with the following statement: if EXISTS (SELECT 1 from tb where id = @ id) print 'exists'- * /
- Delete the 'US' 'data --Exec p_delete 2 - not contains the child, because there is a child in the United States, so remove the EXEC P_DELETE 2, 1 - contain the child, will delete the US and all data GO
In the original text, I will see my post on 9CBS.
Http://expert.9cbs.net/expert/topic/2285/2285830.xml?temp=.1212885
