Код

CREATE FUNCTION dbo.Hierarchy (@ТМЦ CHAR(9)) RETURNS TABLE
  AS RETURN (
   WITH FullDescr (ID, ParentID) AS
  (
     SELECT Спр.ID, Спр.ParentID FROM $Справочник.ТМЦ AS Спр WHERE Спр.ID=@ТМЦ
     UNION ALL
     SELECT ddd.ID, ddd.ParentID FROM $Справочник.ТМЦ AS ddd
     INNER JOIN FullDescr AS fd ON fd.ParentID=ddd.ID
   )  

  SELECT $ВидСправочник36.ТМЦ+ FD.ID AS [Val $Справочник] FROM FullDescr AS FD WHERE FD.ID<>@ТМЦ
   UNION ALL SELECT $ВидСправочник36.СемьиМарокТМЦ+$Спр.Семья AS [Val $Справочник] FROM $Справочник.ТМЦ AS Спр WHERE Спр.ID=@ТМЦ AND NOT ($Спр.Семья=$ПустойИД)
           )
 

Код

	DECLARE @ТМЦ CHAR(9) SET @ТМЦ =?  
	SELECT * FROM dbo.Hierarchy(@ТМЦ )
 

Код

	DECLARE @ТМЦ CHAR(9) SET @ТМЦ =?  
	DECLARE @Hierarchy TABLE(ТМЦ CHAR(13))  
	INSERT INTO @Hierarchy SELECT * FROM dbo.Hierarchy(@ТМЦ)
	SELECT * FROM @Hierarchy