CREATE Function dbo.fnGetNewOrgID (@AppID int) Returns Int
As
Begin declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
if isnull(@l4,'') <> ''
select @ret = ID from Org o inner join org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
else if isnull(@l3 ,'') <> ''
select @ret = ID from Org o inner join org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
else if isnull(@l2,'') <> ''
select @ret = ID from Org o inner join org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
else if isnull(@l1,'') <> ''
select @ret = ID from Org o inner join org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
else
set @ret = 0 return @ret End
As
Begin declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
if isnull(@l4,'') <> ''
select @ret = ID from Org o inner join org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
else if isnull(@l3 ,'') <> ''
select @ret = ID from Org o inner join org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
else if isnull(@l2,'') <> ''
select @ret = ID from Org o inner join org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
else if isnull(@l1,'') <> ''
select @ret = ID from Org o inner join org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
else
set @ret = 0 return @ret End
As
Begin declare @l1 int
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret int
select @l1 = l1, @l2 = l2, @l3 = l3, @l4 = l4 From Applications Where AppID = @AppID
if isnull(@l4,0) <> 0
select @ret = ID from Org o inner join org_lvl4 l4 on l4.Lvl4 = o.NodeName where l4.Org_Lvl4_ID = @l4
else if isnull(@l3,0) <>0
select @ret = ID from Org o inner join org_lvl3 l3 on l3.Lvl3 = o.NodeName where l3.Org_Lvl3_ID = @l3
else if isnull(@l2,0) <> 0
select @ret = ID from Org o inner join org_lvl2 l2 on l2.Lvl2 = o.NodeName where l2.Org_Lvl2_ID = @l2
else if isnull(@l1,0) <> 0
select @ret = ID from Org o inner join org_lvl1 l1 on l1.Lvl1 = o.NodeName where l1.Org_Lvl1_ID = @l1
else
set @ret = 0 return @ret End
你的逻辑就是有问题,是:if @l4 is not null AND @l4 <> '' 而不是 OR其它几个判断同上。
declare @l2 int
declare @l3 int
declare @l4 int
declare @ret intselect @l1=1, @l2=2, @l3 = 3, @l4 = 0if @l4 is not null or @l4 <> ''
print '@l4'
else
if @l3 is not null or @l3 <> ''
print '@l3'
else
if @l2 is not null or @l2 <> ''
print '@l2'
else
if @l1 is not null or @l1 <> ''
print '@l1'
else
print '@l0'