/*
Select * from vefn_GetVersionItemsIncludingDeleted('')
*/
/*****************************************************************************
   Volian Enterprises - Proprietary Information - DO NOT COPY OR DISTRIBUTE
       Copyright 2013 - Volian Enterprises, Inc. All rights reserved.
*****************************************************************************/
Create FUNCTION [dbo].[vefn_GetVersionItemsIncludingDeleted](@DocVersionList varchar(MAX))
RETURNS @VersionItems TABLE
(
 VersionID int,
 ItemID int,
 ContentID int primary key
)
WITH EXECUTE AS OWNER
AS
BEGIN
 BEGIN
 with Itemz([VersionID], [ItemID], [ContentID]) as 
 (Select DV.VersionID, [I].[ItemID], [I].[ContentID]
  FROM [Items] I
  JOIN vefn_DocVersionSplit(@DocVersionList) DV
   ON I.[ItemID] = DV.[ItemID]
 Union All
 -- Children
  select Z.VersionID, I.[ItemID], I.[ContentID]
  from Itemz Z
  join tblParts P on P.ContentID = Z.ContentID
  join tblItems I on I.ItemID = P.ItemID
 Union All
 -- Siblings
  select Z.VersionID, I.[ItemID], I.[ContentID]
  from Itemz Z
  join tblItems I on I.PreviousID = Z.ItemID
 )
 insert into @VersionItems
 select VersionID, [ItemID], [ContentID]
  from ItemZ I
 --Select * From rousages RU Where RU.ContentID in (Select ContentID from ItemZ)
 OPTION (MAXRECURSION 10000)
 END
RETURN
END