存储过程如下：我用递归调用可惜运行错误“名为 'Bom_N' 的游标已存在。”，请教是什么原因？  

create procedure BOMSpread    
 @ItemA ItemCode,@Qty Qty      --WITH ENCRYPTION    
as  
begin  
  delete from BomTemp
  declare @FatherItem ItemCode,@Item ItemCode,@RequireQty Qty      DECLARE Bom_N CURSOR  for select FatherItem,Item,RequireQty from Bom where FatherItem=@ItemA         OPEN Bom_N      FETCH NEXT FROM Bom_N  INTO @FatherItem, @Item, @RequireQty      WHILE @@FETCH_STATUS = 0
  BEGIN         insert into BomTemp (FatherItem, Item, RequireQty,       BomQty)
                    values(@FatherItem,@Item,@RequireQty*@Qty, @RequireQty)  
     exec  BOMSpread @Item,@RequireQty
     FETCH NEXT FROM Bom_N  INTO @FatherItem, @Item, @RequireQty
     if @@FETCH_STATUS<>0
        RETURN 
  END
  CLOSE Bom_N
  DEALLOCATE Bom_N
end
 

hi, cursor 並非是區域變數, 並不是存在於你的 stored procedure 中而已, 而是存在於 connection 中, 所以使用遞歸(recursive)呼叫你的stored procedure 時, 同樣 cursor 的名稱僅能產生一次哦!! 解決之道可能在於將此 recursive 的結構展開, 改為非 recursive 呼叫!

 
create procedure BOMSpread      
@ItemA ItemCode, @Qty Qty, @OrderID OrderID,@OrdLnNo smallint,@ItemLevel int  
  
--WITH ENCRYPTION      
as    
begin    
  declare @FatherItem ItemCode,@Item ItemCode,@RequireQty Qty  
  DECLARE Bom_N CURSOR forward_only local  for   
   select FatherItem,Item,RequireQty from Bom where FatherItem=@ItemA     
  
  OPEN Bom_N  
  
  
  FETCH NEXT FROM Bom_N  INTO @FatherItem, @Item, @RequireQty     
  WHILE @@FETCH_STATUS = 0  
  BEGIN  
     insert into BomTemp (OrderID,OrdLnNo,FatherItem, Item, RequireQty,       BomQty      ,ItemLevel ,Status)--  
                   values(@OrderID,@OrdLnNo,@FatherItem,@Item,@RequireQty*@Qty, @RequireQty,@ItemLevel, 'N') --  
  
     select @ItemLevel=@ItemLevel 1  请问我想得到Item的层次应该怎么解决？
     exec  BOMSpread @Item , @RequireQty, @OrderID ,@OrdLnNo,@ItemLevel 
  
     FETCH NEXT FROM Bom_N  INTO @FatherItem, @Item, @RequireQty  
   
     if @@FETCH_STATUS<>0  
     begin  
        RETURN   
     end  
  END  
  CLOSE Bom_N  
  DEALLOCATE Bom_N  
end  

以上计算出来层次@itemlevel为直接累加，而没判断层次关系，请教该怎么解决？

若是要知道層次, 可以利用一個系統變環境變數 @@NESTLEVEL 來取得該 sp 被呼叫的層次!!    @@NESTLEVEL 傳回現行預存程序執行的巢狀層次 (初始值為 0)。

大大，能不能在我上面的代码里提示一下，谢谢，不太明白你的意思呀