-- NOTE: replace these accordingly
  -- replace columns : MyEntityID, MyParentEntityID
  -- replace parameter : @MyEntityIDParameter
  -- replace table: MyTable
  -- also note that the condition for anchor and recursive part
  -- and the final consumption (select statement after the WITH block)
  -- can be changed dependending on requirement
  WITH DescendantsAndSelf([ID], [ParentID], [Sequence]) AS 
  (
      -- self (anchor part)
      SELECT 
        [ID] = MyEntityID,
        [ParentID] = MyParentEntityID,
        [Sequence] = 1
      FROM MyTable
      WHERE 
		MyEntityID = @MyEntityIDParameter

      UNION ALL

      -- descendants (recursive part)
      SELECT 
        [ID] = MyEntityID,
        [ParentID] = MyParentEntityID,
        [Sequence] = [Sequence] + 1
      FROM DescendantsAndSelf AS CTE, MyTable AS T1
      WHERE CTE.[ID] = T1.MyParentEntityID
  )

  SELECT *
  FROM DescendantsAndSelf