注意：必須把游標聲明為局部的，即加上localCREATE PROCEDURE [dbo].[p_expand_k3_bom]
@p_itemID int, @p_levelID int
WITH EXEC AS CALLER
AS
declare @v_itemID int
declare @v_levelID int
declare @v_itemNO varchar(20)
declare @v_itemName varchar(200)
declare @v_itemModel varchar(200)
declare @v_note1 varchar(20)
declare cursor_bom CURSOR LOCAL FOR select b.FItemID,c.FShortNumber, c.FName, c.FModel, b.FNote1
from AIS20120110144135.dbo.ICBOM a, AIS20120110144135.dbo.ICBOMChild b, AIS20120110144135.dbo.t_ICItem c
where a.FInterID = b.FInterID
and b.FItemID = c.FItemID
and a.FInterID = (select top 1 FInterID from AIS20120110144135.dbo.ICBOM where FItemID = @p_itemID)
and a.FItemID = @p_itemID
set @v_levelID = @p_levelID+1
open cursor_bom
fetch next from cursor_bom into @v_itemID, @v_itemNO, @v_itemName, @v_itemModel, @v_note1
while (@@FETCH_STATUS = 0)
begin
INSERT INTO k3_bom_info
(level_id, item_id, item_no, item_name, item_model, note_1)
VALUES (@v_levelID, @v_itemID, @v_itemNO, @v_itemName, @v_itemModel, @v_note1)
exec p_expand_k3_bom @v_itemID, @v_levelID
fetch next from cursor_bom into @v_itemID, @v_itemNO, @v_itemName, @v_itemModel, @v_note1
end
close cursor_bom
deallocate cursor_bom
GO
posted on 2013-05-14 17:17
Ke 閱讀(872)
評論(0) 編輯 收藏 所屬分類:
sql server