这个存储过程该怎么写(100分)

有一数据表,存放一数状结构
Table jl_Unit
uId int //结点ID
uParentUnitId int //父结点ID
uPower int //结点Power值
我欲实现一个存储过程(以下简称SP), 类似于Set_Power @id,@power
来实现变更某一结点的upower值,并同时变更其子结点的uPower值.
我做了一个SP如下:
Create Procedure [dbo].[sp_SetPower]
@Id int,
@power int
AS
declare
@n_Id int

update jl_Unit set uPower=@power where uId=@Id

declare
my_cursor cursor for select uId from jl_Unit where uParentUnitId=@Id
open my_cursor

fetch next from my_cursor into @n_Id

while @@FETCH_STATUS = 0
begin
Exec sp_SetPower @Id,@power
fetch next from my_cursor into @n_Id
end
close my_cursor
DeAllocate my_cursor
但是我发现递归调用自己的时候my_cursor不是象Delphi的局部变量,而是全局的.
该怎么办呢?

你的sp 有问题吧

Create Procedure [dbo].[sp_SetPower]
@Id int,
@power int
AS
declare
@n_Id int

update jl_Unit set uPower=@power where uId=@Id

declare
my_cursor cursor for select uId from jl_Unit where uParentUnitId=@Id
open my_cursor

fetch next from my_cursor into @n_Id

while @@FETCH_STATUS = 0
begin
Exec sp_SetPower @n_Id,@power --这里应是@n_id 不是@id
fetch next from my_cursor into @n_Id
end
close my_cursor
DeAllocate my_cursor

这样子试

对对对,楼上老兄说的地方我事忽略了,但是这个和全局游标没有关系,问题出在游标上

你的sp 有问题吧

Create Procedure [dbo].[sp_SetPower]
@Id int,
@power int
AS
declare
@n_Id int

update jl_Unit set uPower=@power where uId=@Id

declare
my_cursor cursor LOCAL --加上 local 试试 应可以的
for select uId from jl_Unit where uParentUnitId=@Id
open my_cursor

fetch next from my_cursor into @n_Id

while @@FETCH_STATUS = 0
begin
Exec sp_SetPower @n_Id,@power --这里应是@n_id 不是@id
fetch next from my_cursor into @n_Id
end
close my_cursor
DeAllocate my_cursor

这样子试

解决了,这个问题全怪我不求甚解,没有搞清楚游标的知识.
我改成局部游标就ok了.
CREATE Procedure [dbo].[sp_SetPower]
@Id int,
@power int
AS
declare
@n_Id INT,
@my_cursor CURSOR

update jl_Unit set uPower=@power where uId=@Id

set @my_cursor=cursor for select uId from jl_Unit where uParentUnitId=@Id
open @my_cursor

fetch next from @my_cursor into @n_Id

while @@FETCH_STATUS = 0
begin
Exec sp_SetPower @n_Id,@power
fetch next from @my_cursor into @n_Id
end
close @my_cursor
DeAllocate @my_cursor
GO
但是还请高手看看,指点一二.我刚接触这些,肯定有蹩脚的地方,这100分给您指点费了.

继续问一个问题吧：
为了得到一个Select语句中返回的一个字段的值到一个变量,我现在是这样做的：
Declare
@uId int,
@my_cursor Cursor

Set @my_cursor=Cursor for select max(uId)+1 as 'uid' from jl_Unit
open @my_cursor
fetch @my_cursor into @uId
close @my_Cursor
Deallocate @my_Cursor
可是我觉得应该可以有一条语句类似这样的：
select max(uid) as 'uId' from jl_Unit where uId=uParentUnitId into @uId
这样的语句有吗？还是必须用上面的办法？？？

如果你的 select 中返回值只有一条记录你可以
select @uid=max(uid)+1 from jl_unit

多谢楼上哥们。
我再补充一下吧，如果多个变量的话也可以@var1=Field1,@var2=field2

接受答案了.