GetParentID.sql

--------------------------------------------------------------------------------------------------------------------------------
-- GET PARENT ID (FUNCTION)
--------------------------------------------------------------------------------------------------------------------------------
-- Given a @TopicID, returns the TopicID of the node above it in the Topics nested set hierarchy.
--------------------------------------------------------------------------------------------------------------------------------
CREATE
FUNCTION	[dbo].[GetParentID] (
	@TopicID		INT
)
RETURNS	INT
AS

BEGIN

  ------------------------------------------------------------------------------------------------------------------------------
  -- DECLARE AND DEFINE VARIABLES
  ------------------------------------------------------------------------------------------------------------------------------
  DECLARE	@CurrentParentID	INT

  ------------------------------------------------------------------------------------------------------------------------------
  -- GET PARENT ID FROM HIERARCHY
  ------------------------------------------------------------------------------------------------------------------------------
  SELECT       	@CurrentParentID = (
    SELECT	TOP 1
	TopicID
    FROM	Topics		t2
    WHERE	t2.RangeLeft		< t1.RangeLeft
      AND	t2.RangeRight		> t1.RangeRight
    ORDER BY	t2.RangeRight-t1.RangeRight	ASC
  )
  FROM	Topics		t1
  WHERE	TopicID		= @TopicID
  ORDER BY	RangeRight-RangeLeft	DESC

  ------------------------------------------------------------------------------------------------------------------------------
  -- RETURN VALUE
  ------------------------------------------------------------------------------------------------------------------------------
  RETURN	@CurrentParentID

END