-- =============================================
-- Author:        Krisz
-- Create date:
-- Description: Stored procedure to get the guid path
-- =============================================
CREATE PROCEDURE [dbo].[GetGuidPathFromOrderNum]
    @selectedOrderNum AS INT
    
AS
BEGIN

SET NOCOUNT ON;

-- ********** Select order numbers of the section's parents **********
DECLARE @ParentOrderNumbers TABLE (doc_id varchar(40))
DECLARE @iterator AS INT
DECLARE @doc_id AS varchar(40)

SELECT @doc_id = doc_id FROM dbo.outline WHERE order_num = @selectedOrderNum
INSERT INTO @ParentOrderNumbers (doc_id) VALUES (@doc_id)

WHILE @@ROWCOUNT > 0
BEGIN

    SELECT
    @iterator = PARENT_OUTLINE.order_num
    , @doc_id = PARENT_OUTLINE.doc_id
    FROM outline
    JOIN outline AS PARENT_OUTLINE
        ON outline.parent_id = PARENT_OUTLINE.doc_id
    WHERE
        outline.order_num = @selectedOrderNum

    IF (@@ROWCOUNT > 0)
    BEGIN
        INSERT INTO @ParentOrderNumbers (doc_id) VALUES (@doc_id)
        SET @selectedOrderNum = @iterator
    END
    
END

SELECT * FROM @ParentOrderNumbers
END
GO