Write a stored procedure to find all the staff of an organization and its lower-level organizations.

Below is the database table structure
/*Table: my_org_list*/
Create   Table My_org_list (
Corg_id Varchar ( 36 ) Not   Null ,
Corg_name Varchar ( 50 ) Null ,
Cremark Varchar ( 1024 ) Null ,
Cstatus Int Null ,
Corg_type Int Null ,
Constraint Pk_my_org_list Primary   Key (Corg_id)
)
Go

/*Table: my_org_user*/
Create   Table My_org_user (
Crow_id Varchar ( 36 ) Not   Null ,
Cparent_id Varchar ( 36 ) Not   Null ,
Cchild_id Varchar ( 36 ) Not   Null ,
Ctype Int Null ,
Cgrade_id Varchar ( 36 ) Null ,
Cref_key Int Null ,
Constraint Pk_my_org_user Primary   Key (Crow_id)
)
Go


/*Index: idx_org_user_relation_uq*/
Create Index Idx_org_user_relation_uq On My_org_user (
Cparent_id,
Cchild_id
)
Go

/*Table: my_user_list*/
Create   Table My_user_list (
Cuser_id Varchar ( 36 ) Not   Null ,
Cuser_name Varchar ( 20 ) Null ,
Cname Varchar ( 20 ) Null ,
Cpassword Varchar ( 20 ) Null ,
Cpass_status Int Null ,
Cuser_grade Int Null ,
Csex Varchar ( 4 ) Null ,
Cremark Varchar ( 1024 ) Null ,
Conguard Int Null ,
Cstatus Int Null ,
Cworkfolder Varchar ( 1024 ) Null ,
Constraint Pk_my_user_list Primary   Key (Cuser_id)
)
Go

The following is the stored procedure:Create Procedure Pro_comp_user (
@ I _comp_id Varchar ( 60 )
)

As
-- Search for all employees of the organization and its lower-level organizations
Declare @ Org_table Table
(
Corg_id Varchar ( 36 ),
Clevel Int
)

Declare @ Clevel Int
Declare @ Cparent_id Varchar ( 36 )
Declare @ Cchild_id Varchar ( 36 )
Set @ Cparent_id = @ I _comp_id
Set @ Clevel =   1

Insert   Into @ Org_table
(Corg_id, clevel) Values (@ Cparent_id, @ clevel)

Loops:


Begin


Insert   Into @ Org_table
Select Cchild_id corg_id,
@ Clevel +   1

From My_org_user
Where Cparent_id In  
( Select Corg_id From @ Org_table Where Clevel = @ Clevel) And
Ctype =   1

If @ Rowcount >   0
Begin
Set @ Clevel = @ Clevel +   1
Goto Loops
End

End

Begin
Select C. cname,
C. cuser_name
-- D. corg_id,
-- D. corg_name

From @ Org_table
Inner   Join My_org_user B
On A. corg_id = B. cparent_id
Inner   Join My_user_list C
On B. cchild_id = C. cuser_id
Inner   Join My_org_list d
On A. corg_id = D. corg_id

Where B. ctype =   0

-- Order by cuser_name
End