WITH MenuBranch (Id, [Name], Parent, [Level], [Root]) AS
(
                          SELECT
						section_id, name_en, parent_section, section_level, section_id
				  FROM
						sections
				  WHERE
						parent_section = 0
				  UNION ALL
				  SELECT
						sec.section_id, sec.name_en, sec.parent_section, sec.section_level, [Root]
				  FROM
						sections sec
				  INNER JOIN MenuBranch ON
						sec.parent_section = MenuBranch.Id
			)
			UPDATE MB
			SET [Level] = [Level] + @level_delta
			FROM (SELECT * FROM MenuBranch WHERE [Root] = @section_id) AS MB;

CREATE PROC changeparent
@New_Parent_ID int,
@Item_ID int

AS
UPDATE table
SET PARENT_SECTION=@New_Parent_ID
WHERE SECTION_ID=@Item_ID

ALTER PROCEDURE [dbo].[usp_updatesection]
	@section_id INT,
	@parent_section INT,
	@name_en NVARCHAR(255),
	@name_fr NVARCHAR(255),
	@display_order INT,
	@active BIT,
	@link_en NVARCHAR(255),
	@link_fr NVARCHAR(255),
	@new_page BIT,
	@parent_changed BIT
AS
BEGIN
	DECLARE @section_level AS INT
	DECLARE @old_section_level as INT
	DECLARE @new_section_level as INT
	DECLARE @level_delta as INT

	IF @parent_changed = 0 
		BEGIN
		IF @parent_section = 0 
			BEGIN
				SET @section_level = 1;
			END
		ELSE
			BEGIN
				SELECT @section_level = section_level + 1 
				FROM sections
				WHERE section_id = @parent_section;
			END
		
		BEGIN
			UPDATE sections
			SET section_level = @section_level
				,parent_section = @parent_section
				,name_en = @name_en
				,name_fr = @name_fr
				,display_order = @display_order
				,active = @active
				,link_en = @link_en
				,link_fr = @link_fr
				,new_page = @new_page
			WHERE section_id = @section_id;
		END
		END
	ELSE
		BEGIN
			-- determine section_level delta with new parent
			SELECT @old_section_level = section_level
			FROM sections
			WHERE section_id = @section_id;

			SELECT @new_section_level = section_level + 1 
			FROM sections
			WHERE section_id = @parent_section;

			SET @level_delta = @new_section_level - @old_section_level;

			-- find all child sections of this section (recursively for all children)
			-- update section_level of all children (add delta to current section_level)
			-- CTE (Common Table Expression : http://blogs.msdn.com/simonince/archive/2007/10/17/hierarchies-with-common-table-expressions.aspx
			WITH MenuBranch (Id, [Name], Parent, [Level], [Root]) AS
			(
				  SELECT
						section_id, name_en, parent_section, section_level, section_id
				  FROM
						sections
				  WHERE
						parent_section = 0
				  UNION ALL
				  SELECT
						sec.section_id, sec.name_en, sec.parent_section, section_level, [Root]
				  FROM
						sections sec
				  INNER JOIN MenuBranch ON
						sec.parent_section = MenuBranch.Id
			)
			UPDATE MB
			SET [Level] = [Level] + @level_delta
			FROM (SELECT * FROM MenuBranch WHERE [Root] = @section_id) AS MB;

			-- update info of this section
			IF @parent_section = 0 
				BEGIN
					SET @section_level = 1
				END
			ELSE
				BEGIN
					SELECT @section_level = section_level + 1 
					FROM sections
					WHERE section_id = @parent_section
				END
			
			BEGIN
				UPDATE sections
				SET section_level = @section_level
					,parent_section = @parent_section
					,name_en = @name_en
					,name_fr = @name_fr
					,display_order = @display_order
					,active = @active
					,link_en = @link_en
					,link_fr = @link_fr
					,new_page = @new_page
				WHERE section_id = @section_id
			END
		END

END

WITH MenuBranch (Id, [Name], Parent, [Level], [Root]) AS
			(
				  SELECT
						section_id, name_en, parent_section, section_level, section_id
				  FROM
						sections
				  WHERE
						parent_section = 0
				  UNION ALL
				  SELECT
						sec.section_id, sec.name_en, sec.parent_section, section_level, [Root]
				  FROM
						sections sec
				  INNER JOIN MenuBranch ON
						sec.parent_section = MenuBranch.Id
			)
			UPDATE sections
			SET section_level = section_level + @level_delta
			WHERE section_id IN (SELECT section_id FROM MenuBranch WHERE [Root] = @section_id);