Cacher is the code snippet organizer for pro developers

DECLARE @Path AS varchar(2000) DECLARE @ObjectType AS int DECLARE @AdvertID AS varchar(8)
SET @AdvertID = 'D0128125' ;

 WITH folCTE (ROW, ContainerNodeID, Name, parentContainerNodeID,ObjectType) AS
  ( SELECT 1 ,
           ContainerNodeID ,
           Name ,
           parentContainerNodeID ,
           ObjectType
   FROM dbo.Folders
   WHERE (ContainerNodeID IN
            (SELECT ContainerNodeID
             FROM dbo.FolderMembers
             WHERE InstanceKey = @AdvertID))
     AND (ObjectType NOT IN (7,
                             8))
   UNION ALL SELECT cte.ROW + 1 ,
                              f.ContainerNodeID ,
                              f.Name ,
                              f.parentContainerNodeID ,
                              f.ObjectType
   FROM dbo.Folders f
   INNER JOIN folCTE cte ON cte.parentContainerNodeID = f.ContainerNodeID)
SELECT @Path = COALESCE(@Path + '\', '') + Name from folCTE ORDER BY row DESC

SELECT
 @AdvertID AS 'AdvertID'
 ,ad.AdvertisementName,
 CASE
  WHEN @Path IS NULL THEN 'ROOT'
  ELSE @Path
 END AS 'Path'
FROM v_Advertisement ad
WHERE AdvertisementID = @AdvertID