Select recursivo

DECLARE @work_item_id_inicial INT;
DECLARE @board_id_inicial INT;

SET @work_item_id_inicial = 2945;
SET @board_id_inicial = 259;

WITH RECURSIVA AS (
	SELECT
	  dir.work_item_id,
	  dir.directory_id,
	  dir.board_id,
	  arc.archive_id,
	  arc.archive_original_name,
	  arc.archive_azure_name,
	  arc.archive_ocean_url_description,
	  arc.archive_type
	FROM oce_files_directories dir

	INNER JOIN oce_files_directories_archives dia
	ON dir.directory_id = dia.directory_id

	INNER JOIN oce_files_archives arc
	ON dia.archive_id = arc.archive_id

	WHERE dir.work_item_id = @work_item_id_inicial
	AND dir.board_id = @board_id_inicial

    UNION ALL

	SELECT
	  dir.work_item_id,
	  dir.directory_id,
	  dir.board_id,
	  arc.archive_id,
	  arc.archive_original_name,
	  arc.archive_azure_name,
	  arc.archive_ocean_url_description,
	  arc.archive_type
	FROM oce_files_directories dir

	INNER JOIN RECURSIVA rec
	ON dir.father_directory_id = rec.directory_id

	INNER JOIN oce_files_directories_archives dia
	ON dir.directory_id = dia.directory_id

	INNER JOIN oce_files_archives arc
	ON dia.archive_id = arc.archive_id
)
SELECT *
FROM RECURSIVA;