Classic Stored Procedure (01)-tree Information Update

1. The fields in the dictcomp table are as follows:

2. Stored Procedure content
/* Configure /*------------------------------------------------------------------------------------------------------------------------------
This process is used to "Update tree information to complete background update path"
Input parameters
@ Pcompid
Tree node ID
Outgoing Parameters
@ Presult
= 1 indicates the confirmation is successful.
=-1 indicates that it has been confirmed
=-2 indicates an error occurred during confirmation.
@ Poutstr
Createdate: 2005-08-15 16:26:20 by wujinshu
Lastupdate: 2005-08-15 17:49:20 by wujinshu

Delete from dictcomp where COMPID = 0
Optional ------------------------------------------------------------------------------------------------------------------------------*/
Create procedure proupdatecomppath
(
---- Input parameters ----
@ Pcompid int
)

---- Close the counter ----
-- Set nocount on
Declare
@ Presult int,
@ Poutstr varchar (50)

Declare
@ Errorint int,
@ Errorstr varchar (200 ),

@ COMPID int,
@ Compname varchar (100 ),
@ Parentid int,
@ Haschild int,
@ Idpath varchar (500 ),
@ Namepath varchar (1000 ),
@ Levelindex int,

@ Parentidpath varchar (500 ),
@ Parentnamepath varchar (1000 ),
@ My_lakes_cursor cursor
 
-- Select @ pcompid = 0
 
-- Define a cursor
Declare c_datacursor
Cursor local
Select
COMPID,
Compname,
Parentid,
Haschild,
Idpath,
Namepath,
Levelindex
From
Dictcomp
Where
-- A. COMPID = @ pcompid
-- Or a. parentid = @ pcompid
A. parentid = @ pcompid
Order
A. parentid
For read only

Select @ errorint = 0

If @ pcompid = 0
Begin
Update
Dictcomp
Set
Idpath = cast (COMPID as varchar (20 )),
Namepath = compname,
Levelindex = 0
Where
Parentid = 0
End
Else begin
If exists (select COMPID from dictcomp where parentid = @ pcompid and COMPID <> 0)
Set @ haschild = 1
Else
Set @ haschild = 0

Select
@ Parentidpath = isnull (B. idpath ,''),
@ Parentnamepath = isnull (B. namepath ,''),
@ Levelindex = isnull (B. levelindex, 0 ),
@ Parentid = A. parentid
From
Dictcomp a left join dictcomp B on A. parentid = B. COMPID
Where
A. COMPID = @ pcompid

If @ parentid = 0
Begin
Set @ levelindex = @ levelindex + 1
End
Else begin
Set @ levelindex = @ levelindex + 1
End

Update
Dictcomp
Set
Idpath = @ parentidpath + '/' + Cast (COMPID as varchar (20 )),
Namepath = @ parentnamepath + '/' + compname,
Haschild = @ haschild,
Levelindex = @ levelindex
Where
COMPID = @ pcompid
End

-- Open the cursor
Open c_datacursor

-- Get Data
Fetch c_datacursor
Into
@ COMPID,
@ Compname,
@ Parentid,
@ Haschild,
@ Idpath,
@ Namepath,
@ Levelindex

While (@ fetch_status = 0)
Begin
-- Print @ COMPID
-- Print @ compname
-- Print @ parentid
-- Print '------------------------------------------'
 
If exists (select COMPID from dictcomp where parentid = @ COMPID and COMPID <> 0)
Set @ haschild = 1
Else
Set @ haschild = 0

Update
Dictcomp
Set
Haschild = @ haschild
Where
COMPID = @ COMPID

Select
@ Parentidpath = isnull (B. idpath ,''),
@ Parentnamepath = isnull (B. namepath ,'')
From
Dictcomp a left join dictcomp B on A. parentid = B. COMPID
Where
A. COMPID = @ COMPID
 
If @ parentid = 0
Begin
Set @ levelindex = @ levelindex + 1
End
Else begin
Set @ levelindex = @ levelindex + 1
End

Update
Dictcomp
Set
Idpath = @ parentidpath + '/' + Cast (COMPID as varchar (20 )),
Namepath = @ parentnamepath + '/' + compname,
Levelindex = @ levelindex
Where
Parentid = @ COMPID
 
-- Execute proupdatecomppath @ COMPID, @ presult output, @ poutstr output
Execute proupdatecomppath @ COMPID

-- Get Data
Fetch c_datacursor
Into
@ COMPID,
@ Compname,
@ Parentid,
@ Haschild,
@ Idpath,
@ Namepath,
@ Levelindex
End

-- Close the cursor
Close c_datacursor

-- Release cursor
Deallocate c_datacursor

-- Incorrect judgmentCode, 1 indicates error, 0 indicates no error
If @ errorint = 1
Begin
Select @ presult =-2
Select @ poutstr = @ errorstr
End
Else begin
-- Determine whether an error has occurred
If @ error = 0
Begin
Select @ presult = 1
Select @ poutstr = 'success'
End
Else begin
Select @ presult =-2
Select @ poutstr = 'failed'
End
End

grant exec on proupdatecomppath to Public
go