ALTER FUNCTION  [dbo].[RecursionSysLocation]

(

    -- Add the parameters for the function here

    @ParentId nvarchar(36)

)

RETURNS TABLE

AS

RETURN

(

    with temp ( [Id], [parentid])

    as

    (

        select Id, ParentId

        from SysLocation

        where ParentId = @ParentId

        union all

        select a.Id, a.ParentId

        from SysLocation a

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

    )

    select s.Id, s.ParentId from SysLocation s where Id=@ParentId

    union all

    select * from temp

)