求一个简单的存储过程!

Question

刚学存储过程,希望各位大虾给个标准点的格式!我尽量把需求描述的详细点.无级分类表结构:id    name    path      parentID
----  -----   -------   ---------
1     A       1         0
2     AA      1,2       1
3     AAA     1,2,3     2
按上述表规律
1,给AAA 添加一个子节点 name= 'AAAA' parentID=3
2,取得AAAA的ID
3,取得AAAA 父节点 AAA 的 path
4,更新AAAA 的 path (AAA的parh + "," + AAAA的ID)插入后的效果: 
id    name    path      parentID
----  -----   -------   ---------
4     AAAA    1,2,3,4   3求一个存储过程入参: parentID = 3 (父节点AAA的ID)
      name = 'AAAA'(新节点的名称)出参: nodeID       (新节点的ID)
      nodePath     (新节点的path)

Answer 1

create proc insert_node
@parentID int
@name varchar(20)
asdeclare @path varchar(30)
select @path=path from tablename where id=@parentIDdeclare @id intinsert tablename(name,parentID) values(@Name,@parentID)
set @id=SCOPE_IDENTITY ()if @path is null
   set @path=cast(@id as varchar)
else
   set @path=@path+','+cast(@id as varchar)update tablename
set path=@path
where id=@idgo
   

Answer 2

create table T(id int , name varchar(100), path varchar(100), parentID int)
insert T select 1,     'A',       '1',         0
union all select 2,     'AA',      '1,2',       1
union all select 3,     'AAA',     '1,2,3',     2create proc pc
@name varchar(100),
@parentID int,
@id int output,
@path varchar(100) output
as
select @id=isnull(max(id), 0)+1 from T
select @path=path from T where id=3
if(@path is null)
set @path=rtrim(@id)
else
set @path=@path+','+rtrim(@id)insert T values(@id, @name, @path, @parentID)
godeclare @id int, @path varchar(100)
exec pc 'AAAA', 3, @id output, @path outputselect * from T

Answer 3

--result
id          name                                                                                                 path                                                                                                 parentID    
----------- ---------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- ----------- 
1           A                                                                                                    1                                                                                                    0
2           AA                                                                                                   1,2                                                                                                  1
3           AAA                                                                                                  1,2,3                                                                                                2
4           AAAA                                                                                                 1,2,3,4                                                                                              3(4 row(s) affected)