USE mydb;
GO
CREATE PROCEDURE bng_normalize_ps ( ps_id uniqueidentifier, u_name VARCHAR(50), u_type VARCHAR(50) ) AS
BEGIN

	WITH Recursive (link, n_type, n_name, F_Parent )
	AS
	(
		SELECT
			i.link
			, ityp.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		JOIN ED_Network_Struct s on s.LINK = i.LINK
		WHERE
			ni.link = ps_id
		UNION ALL
		SELECT
			i.link
			, ityp.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		JOIN ED_Network_Struct s on s.LINK = i.LINK
		JOIN Recursive r ON s.F_Parent = r.link

	)
END;

USE OmniUS;
GO
CREATE PROCEDURE bng_normalize_ps 
	@ps_id uniqueidentifier, 
	@u_name VARCHAR(50), 
	@u_type VARCHAR(50) 
AS
BEGIN

	WITH Recursive (link, n_type, n_name, F_Parent )
	AS
	(
		--SELECT intID, intParentID, strDepartmentName
		--FROM tblDepartment e
		--WHERE e.intID = 9 
		--UNION ALL
		--SELECT e.intID, e.intParentID, e.strDepartmentName
		--FROM tblDepartment e
		--	JOIN Recursive r ON e.intParentID = r.intID

		SELECT
			i.link
			, t.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		JOIN ED_Network_Struct s on s.LINK = i.LINK
		WHERE
			i.link = @ps_id
		UNION ALL
		SELECT
			i.link
			, t.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		JOIN ED_Network_Struct s on s.LINK = i.LINK
		JOIN Recursive r ON s.F_Parent = r.link

	)
	SELECT link, n_type, n_name, F_Parent
	FROM Recursive r
END

CREATE PROCEDURE bng_normalize_ps 
	@ps_id uniqueidentifier, 
	@u_name VARCHAR(50), 
	@u_type VARCHAR(50) 
AS
BEGIN

	WITH Recursive (link, n_type, n_name, F_Parent )
	AS
	(
		SELECT
			i.link
			, t.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
			-- , s.F_Child
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		LEFT JOIN ED_Network_Struct s on s.F_Child = i.LINK
		WHERE
			i.link = @ps_id
		UNION ALL
		SELECT
			i.link
			, t.C_Name n_type
			, i.C_Name as n_name
			, s.F_Parent
		FROM ED_Network_Items i
		JOIN ES_Network_Items_Types t on i.F_Network_Items_Types = t.LINK
		JOIN ED_Network_Struct s on s.F_Child = i.LINK
		JOIN Recursive r ON s.F_Parent = r.link

	)

	IF EXISTS(SELECT n_name FROM Recursive r WHERE n_name LIKE '%' + @u_name + '%' AND n_type = @u_type GROUP BY n_name HAVING COUNT(*) > 1)
	BEGIN
		SELECT CONCAT('Удали дубли по ', @u_type, ' ', @u_name)
		RETURN
	END

	SELECT
		link, n_type, n_name, F_Parent
	FROM Recursive r
	SELECT link, n_type, n_name, F_Parent
	FROM Recursive r
	WHERE
		n_name LIKE '%' + @u_name + '%'
		AND n_type = @u_type -- LIKE '%' + @u_type + '%'
END