Function_Sys_User_GetUnderlings(取该用户下级人员）

Create Function Function_Sys_User_GetUnderlings(@RecordID char(36) = null, @IncludeCurrent Bit = 0)

Returns @Tab Table(RecordID char(36), UserName nvarchar(30), RealName nvarchar(30), OrganizationID char(36), Level Int)

As

Begin

  Declare @Level Int

  Set @Level = 0

  If @IncludeCurrent = 1

  Begin

    Insert @Tab([RecordID],[UserName],[RealName],[OrganizationID],[Level])

    Select [RecordID],[UserName],[RealName],[OrganizationID],0 From [Sys_User] Where [RecordID] = @RecordID

  End

  While @Level = 0 Or @@RowCount > 0

  Begin

    Set @Level = @Level + 1

    Insert @Tab([RecordID],[UserName],[RealName],[OrganizationID],[Level])

    Select [RecordID],[UserName],[RealName],[OrganizationID],@Level From [Sys_User] Where (@Level = 1 and (([Mgr] = @RecordID) Or (@RecordID Is Null and [Mgr] Is Null))) Or ([Mgr] In (Select [RecordID] From @Tab Where [Level] = @Level - 1))

  End

  Return

End