@NameLimit THEN RTrim(Left(Name,@NameLimit-1)) + '…' ELSE Left(Name,@NameLimit) END AS [Name] FROM ( -- parent item attributes SELECT Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad),2),@IdLength) AS Id ,'' AS Parent ,Owner AS Owner ,Type AS Type ,Number AS Number ,Coalesce(Revision,PendingRevision) AS Revision ,Coalesce(Lifecycle,PendingLifecycle) AS Lifecycle ,Description AS Name ,0 AS Find ,1 AS Quantity ,@SortPad AS TreeId ,0 AS Level FROM PDXpertDB.viewer.ItemView WHERE ItemId = @ItemId UNION ALL -- lower-level items' attributes SELECT Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad + Replace(B.TreeId,'.','')),2),@IdLength) ,Left(CONVERT(NVARCHAR(32),HashBytes('MD5', @SortPad + Replace(Left(B.TreeId,LEN(B.TreeId)-3),'.','')),2),@IdLength) ,IVChild.Owner ,IVChild.Type ,CAST(IVChild.Number AS nvarchar(10)) ,Coalesce(IVChild.Revision,IVChild.PendingRevision) ,Coalesce(IVChild.Lifecycle,IVChild.PendingLifecycle) ,IVChild.Description ,CAST(SIV.RowId AS nvarchar(10)) ,Cast(SIV.Quantity as Varchar(20)) ,TreeId ,Level FROM BOM B INNER JOIN PDXpertDB.dbo.StructureItem_View SIV ON SIV.Id=B.Id INNER JOIN PDXpertDB.viewer.ItemView IVChild ON IVChild.ItemId = SIV.BasicItem ) MLBOM --WHERE Type = 'Assembly' ORDER BY TreeId ]]> Table2