sql with递归

with temp as

(

select Id, UserId, OfficeID, RoleId, DeptId, IsDelete, IsEnd, ParentId

from [dbo].[DiGui_Demo]

where UserId =1 or OfficeID = 56 or DeptId = 77

union all

select a.Id, a.UserId, a.OfficeID, a.RoleId, a.DeptId, a.IsDelete, a.IsEnd, a. ParentId

from [DiGui_Demo] a

inner join temp on a.[parentid] = temp.[Id]

)

select distinct * from temp where IsEnd = 1

=====================================================

create proc proc_DeleteDict

(

@autoId int

)

begin

with TEC as

(

select * from OA_Dict where AutoId = @autoId

union all

select a.* from OA_Dict a inner join TEC b on a.ParentId = b.AutoId

)

update OA_Dict set IsDelete = 1 where AutoId in

(

select autoid from TEC

)

end