Welcome to the Lounge

ALTER PROCEDURE [dbo].[spGetChildPages]
    @PageID UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;
    DECLARE @Empty UNIQUEIDENTIFIER;
    SET @Empty = dbo.fGetEmptyGuid();
    WITH Children AS(
        SELECT * FROM Pages WHERE ID = @PageID AND ID != @Empty
        UNION ALL
        SELECT p.* FROM Pages p
           INNER JOIN Children c ON p.ParentID = c.ID
    )
    SELECT * FROM Children WHERE ID != @PageID;
END

ALTER PROCEDURE [dbo].[spGetParentPages]
    @PageID UNIQUEIDENTIFIER
AS
BEGIN
	SET NOCOUNT ON;
    DECLARE @Empty UNIQUEIDENTIFIER;
    SET @Empty = dbo.fGetEmptyGuid();
    WITH Parents AS (
       SELECT *, 1 AS Level FROM Pages WHERE ID = @PageID AND ParentID != @Empty
       UNION ALL
       SELECT p.*, Level+1 AS Level FROM Pages p
	       INNER JOIN Parents pp ON p.Id = pp.ParentId AND pp.ID != @Empty
    )
    SELECT ID, PageName, ParentID, SetID
    FROM Parents 
    WHERE ID != @Empty AND ID != @PageID
    ORDER BY level DESC
END

Jörgen Andersson wrote:

I don't use them

OriginalGriff wrote:

a morning spent working out SQL SP's to "find all Child" and "find all Parent" rows