34,838
社区成员




Insert into skyUserDepartment(UserID,DepID)
select 'IHH101348',DepID
from dbo.DepIDtosubDepID('HH000000AAAA0000')--我就是这里不明白,数据库中没有dbo.DepIDtosubDepID表,这里什么意思呢??where DepID not in (select DepID from skyUserDepartment where UserID='IHH101348')
returns @Temp table(DepID varchar(20) collate SQL_Latin1_General_CP1_CI_AS null,
DepGrade varchar(4) collate SQL_Latin1_General_CP1_CI_AS null)
create function DepIDtoSubDepID(@DepID VARCHAR(20))
returns @Temp table(DepID varchar(20) collate SQL_Latin1_General_CP1_CI_AS null,
DepGrade varchar(4) collate SQL_Latin1_General_CP1_CI_AS null)
as
begin
declare @DepGrade varchar(4),@Grade int
select @DepGrade = DepGrade ,
@Grade = CASE @DepGrade
when 'A1' then 1
when 'A2' then 2
when 'A3' then 3
when '01' then 4
when '02' then 5
when '03' then 6
else 0 end
from Department where DepID = @DepID
insert into @Temp
select @DepID,@DepGrade
where @DepID in (select DepID from Department)
while(@Grade <6)
begin
insert into @Temp
select DepID,DepGrade
from Department
where adminid in (select DepID from @Temp)
and DepID not in (select DepID from @Temp)
and Isnull(Disabled,0)=0
set @Grade = @Grade + 1
end
return
end