CREATE PROCEDURE [dbo].[InsertOutline]
    @outline_doc_id AS VARCHAR(40),
    @outline_depth AS int,
    @outline_parent_id AS VARCHAR(40)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @outline_order_num AS int
    DECLARE @next_parent_order_num AS int
    DECLARE @last_sibling_order_num AS int

    select top 1 @outline_order_num=outline.order_num
                from outline
                where @outline_depth = outline.depth
                and @outline_doc_id = outline.doc_id
                and @outline_parent_id = outline.parent_id

    if (@outline_order_num is null) -- new item
    BEGIN

        SELECT @last_sibling_order_num= MAX(outline.order_num) FROM OUTLINE
            where (@outline_depth = outline.depth and @outline_parent_id = outline.parent_id)
            
        SELECT TOP 1 @outline_order_num    =     order_num
                         FROM          outline
                         WHERE      (depth < @outline_depth) AND (order_num > @last_sibling_order_num)
                         ORDER BY order_num                

        if (@outline_order_num is null)
            SELECT @outline_order_num= COALESCE (MAX(outline.order_num), 0)+1 FROM OUTLINE
         
        -- frees the selected orderNum position
        if exists(select top 1 * from outline where @outline_order_num = outline.order_num)
        update outline set outline.order_num= outline.order_num+1 where @outline_order_num <= outline.order_num

        -- insert to @outline_order_num position
        INSERT INTO outline (order_num, doc_id, depth, parent_id)
        VALUES (@outline_order_num, @outline_doc_id, @outline_depth, @outline_parent_id)
    END
END
GO