ALTER Function dbo.Update_Price(@Code_No varchar(50),@U_Price Numeric(19,3),@Rate Numeric(19,2))
Returns varchar(500) As
Begin
Declare @Result Numeric(19,3),@Total Numeric(19,3),@Code_No2 Varchar(50) Declare Myco Cursor local --定义为局部游标
For
Select b.Code_No From Mrp_Modec a ,Mrp_Mode b where a.single_no=b.single_no and In_Code= @Code_No
Open Myco
Fetch Next From Myco Into @Code_No2
While @@Fetch_status=0
Begin
--判断是原材料,还是标准品
If exists(select * from inventory Where code_no=@Code_No) or exists(select * from Mrp_Mode where code_no=@Code_No )
begin
Select @Total=Sum(a.Sta_Qty*a.U_Price) From Mrp_Modec a, Mrp_Mode b Where a.single_no=b.single_no and b.Code_No=@Code_No2
return @Code_No2+dbo.Update_Price(@Code_No2 ,@U_Price ,@Rate )
end
--else return 0
Fetch Next From Myco Into @Code_No2 End
return ''
end
Returns varchar(500) As
Begin
Declare @Result Numeric(19,3),@Total Numeric(19,3),@Code_No2 Varchar(50) Declare Myco Cursor local --定义为局部游标
For
Select b.Code_No From Mrp_Modec a ,Mrp_Mode b where a.single_no=b.single_no and In_Code= @Code_No
Open Myco
Fetch Next From Myco Into @Code_No2
While @@Fetch_status=0
Begin
--判断是原材料,还是标准品
If exists(select * from inventory Where code_no=@Code_No) or exists(select * from Mrp_Mode where code_no=@Code_No )
begin
Select @Total=Sum(a.Sta_Qty*a.U_Price) From Mrp_Modec a, Mrp_Mode b Where a.single_no=b.single_no and b.Code_No=@Code_No2
return @Code_No2+dbo.Update_Price(@Code_No2 ,@U_Price ,@Rate )
end
--else return 0
Fetch Next From Myco Into @Code_No2 End
return ''
end
找A应返回(H,D,F,J)
但以上只能找到(H,D,F)而J找不到请帮忙
create bb(code_no varchar(50),SubCode varchar(50) )
create FUNCTION dbo.GetSubClass(@Code_No varchar(50),@IdStr varchar(8000)='',@LevelCount int=-1)
/*
参数: @Code_No ,被搜索子类的ID
@IdStr,一个特殊参数,用于在递归中传数据,注意:调用函数时一定要传入‘’空值
@LevelCount 用于判断是不是递归调用的最上层
*/
RETURNS Varchar(8000)
AS
BEGIN Declare @single_no varchar(50), @TC_ID varchar(50),@TC_PID Varchar(50),@StartLevel int,@Id32 int,@Ma_price Numeric(19,3)if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1
If @IdStr='' Set
@IdStr=''''+@InputId +''''
DECLARE TreeClass CURSOR local FOR --定义游标
Select b.single_no,b.Code_No From Mrp_Modec a ,Mrp_Mode b where a.single_no=b.single_no and In_Code= @InputId
OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @single_no,@TC_IDWHILE @@FETCH_STATUS = 0 --循环游标,即循环当前类的弟一级子类
BEGIN
if exists(select * from mrp_modec )
select @Ma_price=Sum(sta_qty*u_price) from Mrp_Modec Where single_no=@single_no
update 表名 SET 字段= @Ma_price WHERE single_no=@single_no --这里为什么通不过
select @IdStr= @IdStr+','+cast(@tC_ID as varchar)+''''
if @@NESTLEVEL<32
set @IdStr= dbo.GetSubClass(@TC_ID,@IdStr,@LevelCount) --递归。
else
set @IdStr='['+cast(@tC_ID as varchar)+']'+@IdStr
FETCH NEXT FROM TreeClass iNTO @single_no,@tC_ID EndCLOSE TreeClass
DEALLOCATE TreeClass/*while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,2,charindex(']',@IdStr)-2)
set @IdStr=dbo.FN_32GetSubClass (@Id32,@IdStr,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end
*/
Return @IdStrEND