自定义函数没有返回值;CREATE Function Flintgrp_Bom_GetCost1(@BomId varchar(12))
Returns Float(8)
as
begin
Declare @tInvCode Varchar(16),@tBomID varchar(12),@tBaseQtyN Float(8),@tBaseQtyD Float(8),@NewPrice Float(8),@tCost Float(8)set @tInvCode=null
set @tBomID=null
set @tCost=0
set @NewPrice=0
set @tBaseQtyN=0
set @tBaseQtyD=0declare cur_Bom_main1 cursor for
select InvCode,BaseQtyN,BaseQtyD from v_bom_opcomponent_rpt where BomId=@BomID order by InvCode
open cur_Bom_main1
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyDWhile @@Fetch_Status = 0
begin
if substring(@tInvCode,1,1)='1'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version>a.version)) a where InvCode =@tInvCode
if @tBomID<>NULL
begin --Flintgrp_Bom_GetCost_End
set @tCost =@tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='2'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version>a.version)) a where InvCode =@tInvCode
if @tBomID<>NULL
begin
set @tCost = @tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='3'
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyD
end --结束游标大循环 处理每一行的数据
close cur_Bom_main1
deallocate cur_Bom_main1Return @tCost
end
我用过substring(@tInvCode,1,1)='2'的数据试过,都没有返回值!晕!
Returns Float(8)
as
begin
Declare @tInvCode Varchar(16),@tBomID varchar(12),@tBaseQtyN Float(8),@tBaseQtyD Float(8),@NewPrice Float(8),@tCost Float(8)set @tInvCode=null
set @tBomID=null
set @tCost=0
set @NewPrice=0
set @tBaseQtyN=0
set @tBaseQtyD=0declare cur_Bom_main1 cursor for
select InvCode,BaseQtyN,BaseQtyD from v_bom_opcomponent_rpt where BomId=@BomID order by InvCode
open cur_Bom_main1
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyDWhile @@Fetch_Status = 0
begin
if substring(@tInvCode,1,1)='1'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version>a.version)) a where InvCode =@tInvCode
if @tBomID<>NULL
begin --Flintgrp_Bom_GetCost_End
set @tCost =@tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='2'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version>a.version)) a where InvCode =@tInvCode
if @tBomID<>NULL
begin
set @tCost = @tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='3'
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyD
end --结束游标大循环 处理每一行的数据
close cur_Bom_main1
deallocate cur_Bom_main1Return @tCost
end
我用过substring(@tInvCode,1,1)='2'的数据试过,都没有返回值!晕!
还是要先
declare @tempCost
select @tempCost=dbo.Flintgrp_Bom_GetCost2(@tBomID)
再
set @tCost = @tCost+@tempCost)*(@tBaseQtyN/@tBaseQtyD)
???????????????????
小梁,那是刚开始工作时同事叫的,现在都叫大梁了!
Returns Float(8)
as
begin
Declare @tInvCode Varchar(16),@tBomID varchar(12),@tBaseQtyN Float(8),@tBaseQtyD Float(8),@NewPrice Float(8),@tCost Float(8) set @tInvCode=null
set @tBomID=null
set @tCost=0
set @NewPrice=0
set @tBaseQtyN=0
set @tBaseQtyD=0 declare cur_Bom_main1 cursor for
select InvCode,BaseQtyN,BaseQtyD from v_bom_opcomponent_rpt where BomId=@BomID order by InvCode
open cur_Bom_main1
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyD While @@Fetch_Status = 0
begin
if substring(@tInvCode,1,1)='1'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version> a.version)) a where InvCode =@tInvCode
if @tBomID <> NULL
begin --Flintgrp_Bom_GetCost_End
set @tCost =@tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='2'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version> a.version)) a where InvCode =@tInvCode
if @tBomID <> NULL
begin
set @tCost = @tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='3'
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
Fetch next from cur_Bom_main1
Into @tInvCode,@tBaseQtyN,@tBaseQtyD
end --结束游标大循环 处理每一行的数据
close cur_Bom_main1
deallocate cur_Bom_main1 Return @tCost
end ------------------
顶
树上的鸟儿 说的
任何数与null 比较都为 false 与NULL 比较 要用 IS NULL 或 IS NOT NULL
IF 1<>NULL
BEGIN
SELECT 1
END
ELSE
BEGIN
SELECT 2
END-----------
2(1 行受影响)
但结果还是一样,没有返回值!急呀!
仔细看了下函数,甚是不解。先不说<>null该不该改成is not null。。
if substring(@tInvCode,1,1)='1'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version> a.version)) a where InvCode =@tInvCode
if @tBomID <> NULL
begin
set @tCost =@tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if substring(@tInvCode,1,1)='2'
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version> a.version)) a where InvCode =@tInvCode
if @tBomID <> NULL
begin
set @tCost = @tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end 这个地方当substring(@tInvCode,1,1)='2'或者='1'时,所执行的操作。我看不出有什么两样。为什么还要分开写呢??
难道不觉得代码很长么?
楼主执行看看,有什么结果:
CREATE Function Flintgrp_Bom_GetCost1(@BomId varchar(12))
Returns Float(8)
as
begin
Declare @tInvCode Varchar(16),@tBomID varchar(12),@tBaseQtyN Float(8),@tBaseQtyD Float(8),@NewPrice Float(8),@tCost Float(8)
declare cur_Bom_main1 cursor for select InvCode,BaseQtyN,BaseQtyD from v_bom_opcomponent_rpt where BomId=@BomID order by InvCode
open cur_Bom_main1
Fetch next from cur_Bom_main1 Into @tInvCode,@tBaseQtyN,@tBaseQtyD
While @@Fetch_Status = 0
begin
if (left(@tInvCode,1)='1') or (left(@tInvCode,1)='2')
begin
select @tBomID=BomID from (select InvCode,BomID from v_BOM_BomParent a where not exists(select 1 from v_BOM_BomParent where InvCode=a.InvCode and version> a.version)) a where InvCode =@tInvCode
if isnull(@tBomID,'')<>''
begin
set @tCost =@tCost+dbo.Flintgrp_Bom_GetCost2(@tBomID)*(@tBaseQtyN/@tBaseQtyD)
end
else
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
end
if left(@tInvCode,1)='3'
begin
select @NewPrice=cInvDefine13 from Inventory where cInvCode=@tInvCode
set @tCost = @tCost+@NewPrice*(@tBaseQtyN/@tBaseQtyD)
end
Fetch next from cur_Bom_main1 Into @tInvCode,@tBaseQtyN,@tBaseQtyD
end
close cur_Bom_main1
deallocate cur_Bom_main1
Return @tCost
end