ALTER PROCEDURE [dbo].[mp_TreeRecalc]
@ARG varchar( 50 ),
@RefID int,
@Level int,
@LeftKey int OUTPUT,
@RightKey int OUTPUT,
@RecCount int OUTPUT
as


SET @Level = @Level +  1 

DECLARE @Qry nvarchar( 4000 ), @s varchar( 20 ), @tbl varchar( 53 ), @pk varchar( 50 )

set @tbl = @ARG

select	top  1   @pk = c.column_name
from	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on c.constraint_name = b.unique_constraint_name
where	c.table_name = @tbl

select @s = cast(@RefID as varchar( 20 ))

SET @Qry = '
DECLARE @' + @pk + ' int, @LeftKey int, @RightKey int, @Level int, @RK int, @RecCount int
SET @LeftKey = ' + cast(@LeftKey as varchar( 10 )) + '
SET @RightKey = ' + cast(@RightKey as varchar( 10 )) + '
SET @Level = ' + cast(@Level as varchar( 10 )) + '

DECLARE treecursor' + @s + ' CURSOR fast_forward FOR
SELECT ' + @pk + '
FROM  ' + @tbl + ' 
WHERE (RefID = ' + @s + ')
/*ORDER BY Name_RU*/

OPEN treecursor' + @s + '

FETCH next FROM treecursor' + @s + ' INTO @' + @pk + '

WHILE @@FETCH_STATUS = 0 BEGIN
	SET @LeftKey = @RightKey + 1
	SET @RightKey = @LeftKey + 1

	UPDATE ' + @tbl + '
	SET [Level] = @Level, LeftKey = @LeftKey
	WHERE (' + @pk + ' = @' + @pk + ')
	SET @RK = @RightKey - 1
	EXEC mp_TreeRecalc ''' + @ARG + ''', @' + @pk + ', @Level, @LeftKey out, @RK out, @RecCount out
	IF (@RK = @RightKey - 1) 
		SET @RightKey = @RK + 1 
	ELSE 
		SET @RightKey = @RK 
	UPDATE ' + @tbl + '
	SET RightKey = @RightKey, ChildCount = @RecCount
	WHERE (' + @pk + ' = @' + @pk + ')
	FETCH next FROM treecursor' + @s + ' INTO @' + @pk + '
END

CLOSE treecursor' + @s + '
DEALLOCATE treecursor' + @s 
EXEC (@Qry)

set @Qry = 'SELECT @RecCount = COUNT(' + @pk + ') FROM ' + @tbl + ' WHERE RefID = ' + cast(@RefID as varchar( 50 ))
exec sp_executesql @Qry, N'@RecCount int out', @RecCount = @RecCount out

IF @RecCount >  0  BEGIN
	set @Qry = 'SELECT @LeftKey = MAX(LeftKey) + 1, @RightKey = MAX(RightKey) + 1
		FROM ' + @tbl +  ' WHERE RefID = ' + cast(@RefID as varchar( 50 ))
	--print @Qry
	exec sp_executesql @Qry, N'@LeftKey int out, @RightKey int out', 
		@LeftKey = @LeftKey out, @RightKey = @RightKey out

END

ALTER PROCEDURE [dbo].[mp_TreeMove]
@ARG varchar( 50 ),
@ID int, 
@new_parent_id int = - 1 , 
@id_befor int = - 1 ,
@Step int =  0 
as 

SET NOCOUNT ON 

declare @Qry nvarchar( 4000 ), @tbl as varchar( 53 ), @pk varchar( 50 ), @IDVal varchar( 50 )

set @tbl = @ARG

select	top  1   @pk = c.column_name
from	INFORMATION_SCHEMA.KEY_COLUMN_USAGE c 
inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS b on c.constraint_name = b.unique_constraint_name
where	c.table_name = @tbl


	--IF (@new_parent_id = -1) and (@id_befor = -1)  RETURN
	DECLARE @Level varchar( 50 ), @LeftKey varchar( 50 ), @RightKey varchar( 50 ), @RefID varchar( 50 )
	DECLARE @level_up varchar( 50 ), @parent_Lk varchar( 50 ), @parent_Rk varchar( 50 ), @Rk_near varchar( 50 ) 

	--Ключи и уровень перемещаемого узла 
	set @Qry = 'SELECT @Level = [Level], @LeftKey = LeftKey, @RightKey = RightKey, @RefID = RefID FROM ' + @tbl + ' WHERE ' + @pk + ' = ' + @IDVal
	exec sp_executesql @Qry, N'@Level varchar(50) out, @LeftKey varchar(50) out, @RightKey varchar(50) out, @RefID varchar(50) out', 
		@Level = @Level out, @LeftKey = @LeftKey out, @RightKey = @RightKey out, @RefID = @RefID out

	--Ключи и уровень нового родительского узла 
	IF (@new_parent_id <> - 1 ) BEGIN
		set @Qry = 'SELECT @level_up = [Level] FROM ' + @tbl + ' WHERE (' + @pk + ' = ' + cast(@new_parent_id as varchar( 50 )) + ')'
		exec sp_executesql @Qry, N'@level_up varchar(50) out', @level_up = @level_up out

		set @Qry = 'UPDATE	' + @tbl + ' SET RefID = ' + cast(@new_parent_id as varchar( 50 )) + ' WHERE ' + @pk + ' = ' + @IDVal 
		exec (@Qry)
	END ELSE 
		IF (@RefID >  0 ) begin
			set @Qry = 'SELECT @level_up = [Level] FROM ' + @tbl + ' WHERE (' + @pk + ' = ' + @RefID + ')'
			exec sp_executesql @Qry, N'@level_up varchar(50) out', @level_up = @level_up out
		end ELSE
			SELECT @level_up =  1  

	IF (@new_parent_id = - 1 ) BEGIN
		IF (@id_befor = - 1 ) BEGIN --select @RK_near = LeftKey + 1 from tblOwnerCtlg where OwnerCtlgID = @new_parent_id 
			IF (@Step = - 1 )	begin
				set @Qry = 'SELECT @Rk_near = RightKey FROM ' + @tbl + ' WHERE ([Level] = ' + @Level + ') AND (LeftKey = ' + @RightKey + ' + 1)'
				exec sp_executesql @Qry, N'@Rk_near varchar(50) out', @Rk_near = @Rk_near out
			end
			IF (@Step =  1 )	BEGIN 
				set @Qry = 'SELECT @Rk_near = RightKey FROM ' + @tbl + ' WHERE  ([Level] = ' + @Level + ') AND (RightKey = (SELECT LeftKey - 1 FROM ' + @tbl + ' WHERE RightKey = ' + @LeftKey + ' - 1))'
				exec sp_executesql @Qry, N'@Rk_near varchar(50) out', @Rk_near = @Rk_near out
				IF (@Rk_near IS NULL) begin
					set @Qry = 'SELECT @Rk_near = LeftKey FROM ' + @tbl + ' WHERE ([Level] = ' + @Level + ' - 1) AND (LeftKey = (SELECT LeftKey - 1 FROM ' + @tbl + ' WHERE RightKey = ' + @LeftKey + ' - 1))'
					exec sp_executesql @Qry, N'@Rk_near varchar(50) out', @Rk_near = @Rk_near out
				end
			END
			IF (@Step =  0 ) or (@Rk_near IS NULL) SELECT @Rk_near = @LeftKey -  1 
		END
	END
	ELSE BEGIN
		IF (@id_befor = - 1 ) begin --select @RK_near = LeftKey + 1 from tblOwnerCtlg where OwnerCtlgID = @new_parent_id 
			set @Qry = 'SELECT @Rk_near = RightKey - 1 FROM ' + @tbl + ' WHERE (' + @pk + ' = ' + cast(@new_parent_id as varchar( 50 )) + ')' 
			exec sp_executesql @Qry, N'@Rk_near varchar(50) out', @Rk_near = @Rk_near out
		end ELSE begin
			set @Qry = 'SELECT @Rk_near = RightKey FROM ' + @tbl + ' WHERE (' + @pk + ' = ' + cast(@id_befor as varchar( 50 )) + ')'
			exec sp_executesql @Qry, N'@Rk_near varchar(50) out', @Rk_near = @Rk_near out
		end
	END
	--делаем по необходимости корректировку 
	IF (@Rk_near is null) SELECT @Rk_near = '0' 
	PRINT @Rk_near
	--Определяем хар-ки перемещения 
	DECLARE @skew_level varchar( 50 ), @skew_tree varchar( 50 ) 
	SET @skew_level =CAST(cast(@level_up as int) - cast(@Level as int) +  1  AS varchar( 50 ))
	SET @skew_tree = cast(cast(@RightKey as int) - cast(@LeftKey as int) +  1  as varchar( 50 ))

	--Перемещаем 
	DECLARE @skew_edit varchar( 50 ) 
	IF (cast(@RightKey as int) < cast(@Rk_near as int)) BEGIN 
		SET @skew_edit = cast(@Rk_near as int) - cast(@LeftKey as int) +  1  - @skew_tree 
		--PRINT @skew_edit
		set @Qry = 'UPDATE	' + @tbl + ' 
		SET		LeftKey =  CASE WHEN RightKey <= ' + @RightKey + ' THEN LeftKey + ' + @skew_edit + ' ELSE CASE WHEN LeftKey > ' + @RightKey + ' THEN LeftKey - ' + @skew_tree + ' ELSE LeftKey END END, 
				[Level] = CASE WHEN RightKey <= ' + @RightKey + ' THEN [Level] + ' + @skew_level + ' ELSE [Level] END, 
				RightKey =  CASE WHEN RightKey <= ' + @RightKey + ' THEN RightKey + ' + @skew_edit + '  ELSE CASE WHEN RightKey <= ' + @Rk_near + ' THEN RightKey - ' + @skew_tree + ' ELSE RightKey END END 
		WHERE	(RightKey > ' + @LeftKey + ') and 
				(LeftKey <= ' + @Rk_near + ')'
	END ELSE BEGIN 
		SET @skew_edit = cast(@Rk_near as int) - cast(@LeftKey as int) +  1  
		--PRINT @skew_edit
		set @Qry = 'UPDATE	' + @tbl + ' 
		SET		RightKey =  CASE WHEN LeftKey >= ' + @LeftKey + ' THEN RightKey + ' + @skew_edit + ' ELSE CASE WHEN RightKey < ' + @LeftKey + ' THEN RightKey + ' + @skew_tree + ' ELSE RightKey END END, 
				[Level] = CASE WHEN LeftKey >= ' + @LeftKey + ' THEN [Level] + ' + @skew_level + ' ELSE [Level] END, 
				LeftKey =  CASE WHEN LeftKey >= ' + @LeftKey + ' THEN LeftKey + ' + @skew_edit + ' ELSE CASE WHEN LeftKey > ' + @Rk_near + ' THEN LeftKey + ' + @skew_tree + ' ELSE LeftKey END END 
		WHERE	(RightKey > ' + @Rk_near + ') and 
				(LeftKey < ' + @RightKey + ')'
	END 
	exec(@Qry)