---函数：ZS_FFindParent_Level Builded By ZS 2005-05-08 15:18 PM AT SUNGANG OFFICE
---意图：用自定义函数查找商品分类树形结构表中某一对象ID从root（-1级）开始的N级对象ID
---root：-1级 level -1，分类法：0级 level 0，分类：1级 level 1。
---参数：@VID ：子对象ID ，@VLevel
---返回：指定级的父对象ID
ALTER FUNCTION ZS_FFindParent_Level(@VID Integer,@VLevel Integer)
RETURNS Integer
AS
BEGIN
declare @VPARNT_ID int --分类法 level 0
declare @nLEVEL int
set @nLEVEL = 1
set @VLEVEL = abs(@VLEVEL)
select @VPARNT_ID = case when ZPARNT_ID = -1 then @VID
when ZPARNT_ID = 721 then 721
else ZPARNT_ID
end
from CO.INV_CLASS where ZINV_CLASS_ID = @VID
if @VPARNT_ID not in (-1,721)
while @VPARNT_ID<>-1 or @VPARNT_ID <>721
begin
if (select ZPARNT_ID from CO.INV_CLASS where ZINV_CLASS_ID = @VPARNT_ID)=-1 or
(select ZPARNT_ID from CO.INV_CLASS where ZINV_CLASS_ID = @VPARNT_ID)=721
break
else
select @VPARNT_ID = ZPARNT_ID from CO.INV_CLASS where ZINV_CLASS_ID=@VPARNT_ID
set @nLEVEL = @nLEVEL + 1
end
set @VPARNT_ID = @VID
if @nLEVEL - @VLEVEL > 0
begin
set @nLEVEL = (@nLEVEL - @VLEVEL)
while @nLEVEL <> 0
begin
select @VPARNT_ID = ZPARNT_ID from CO.INV_CLASS where ZINV_CLASS_ID=@VPARNT_ID
set @nLEVEL = @nLEVEL - 1
end
end
RETURN(@VPARNT_ID)
END
