spCustomFormElement_GetAllChildrenByParentID

        CREATE procedure [dbo].[spCustomFormElement_GetAllChildrenByParentID]
              @ParentID uniqueidentifier
        as

          with CustomFormElementHierarchy (ID, TypeID, SubTypeID, Title, Label, [Description], Instruction, [Guid], [ParentID], [Sequence], [Properties], HierarchyLevel, [FieldTypeForValidation], [DateCreated], [IsComplete]) as
          (
              select
                  [ID],
                  [TypeID],
                  [SubTypeID],
                  [Title],
                  [Label],
                  [Description],
                  [Instruction],
                  [Guid],
                  [ParentID],
                  [Sequence],
                  [Properties],
                  1 as HierarchyLevel,
                  [FieldTypeForValidation],
                  [DateCreated],
                  [IsComplete]
              from
                  dbo.CustomFormElement 
              where
                  ParentID = @ParentID

              union all

              select
                  e.[ID],
                  e.[TypeID],
                  e.[SubTypeID],
                  e.[Title],
                  e.[Label],
                  e.[Description],
                  e.[Instruction],
                  e.[Guid],
                  e.[ParentID],
                  e.[Sequence],
                  e.[Properties],
                  eh.HierarchyLevel + 1 as HierarchyLevel,
                  e.[FieldTypeForValidation],
                  e.[DateCreated],
                  e.[IsComplete]
              from
                  dbo.CustomFormElement e 
              inner join CustomFormElementHierarchy eh on
                  e.ParentID = eh.[Guid]
          )

          select *
          from CustomFormElementHierarchy
          order by HierarchyLevel, ParentID, Sequence ASC