SQL判断成员是否在部门列表中

部门树形层级存储，遍历部门列表的子部门层级一下的数据，一个个匹配，使用临时列表存储每一层的部门数据，Fun_StringToIntArray把需要查询的多个部门信息转为列表

DECLARE @trainObj               INT
set @trainObj = 1004
DECLARE @userId             INT
set @userId = 4869
DECLARE @aa             NVARCHAR(500)

DECLARE @depart TABLE(departId INT , id INT)
INSERT INTO  @depart SELECT [item],row_number() over (order by item) from Fun_StringToIntArray('1,54,789,456,123')
DECLARE @temp TABLE(subCompanyId INT , level INT)
        DECLARE @level INT
        SET @level = 1

        INSERT INTO  @temp SELECT @trainObj , @level
        WHILE @@ROWCOUNT > 0
        BEGIN
            SET @level = @level + 1

            INSERT INTO 
            @temp 
            SELECT 
            a.[departmentId] , @level
            FROM 
            [department_data] a , @temp b
            WHERE 
                      a.[pId] = b.[subCompanyId] 
            AND
             b.[level] = @level - 1
        END
            IF EXISTS (SELECT 0 FROM @temp A,[user_data] B WHERE B.[departmentId] = [subCompanyId] AND B.[userId] = @userId)
            BEGIN
                SELECT CAST(1 AS BIT)
            END
            ELSE
            BEGIN
                SELECT CAST(0 AS BIT)
            END