پیدا کردن والد های یک فرزند

[mazoolagh]

همچنین میتونیم بجای برگرداندن رکوردها، مسیرها رو برگردونیم:

USE [Iran]

GO



SET ANSI_NULLS ON

GO



SET QUOTED_IDENTIFIER ON

GO



CREATE PROCEDURE [dbo].[GetParentsPath]

    @ID int

AS

BEGIN

    SET NOCOUNT ON;



WITH All_Parents(ID) AS

(

    SELECT ParentID FROM Locations WHERE ID=@ID

    UNION ALL

    SELECT L.ParentID

        FROM All_Parents 

        INNER JOIN Locations AS L

        ON All_Parents.ID=L.ID

)



SELECT 

    STRING_AGG (RESULTS.ID , '/') AS [PATH],

    STRING_AGG (RESULTS.[Location] , '/') AS [PATH$]

FROM 

    (SELECT L.ID AS ID, L.ParentID, L.[Location]

    FROM All_Parents

    INNER JOIN Locations AS L

    ON All_Parents.ID = L.ID) AS RESULTS



END

GO

EXEC GetParentsPath    @ID = 1029852

5.png