ClearingFiles.sql

declare @stmt nvarchar(max)
declare @restriction nvarchar(max) 
declare @companyID int
declare @parent int
 
declare c cursor for 
	select CompanyID from Company c 
	where c.CompanyID > 0 
		and(Select top 1 CompanyID from Company Where CompanyId > 0 and ParentCompanyID = c.CompanyID) is null
	order by CompanyID
open c
fetch c into @companyID
while @@fetch_status >= 0 begin
  
	set @restriction = null
	  --finding parents
	SET @parent = @companyID
	while @parent is not null
	begin
		if (@parent is not null)
		BEGIN
			if @restriction is null
				set @restriction = N'(' + convert(varchar(10), @parent)
			else if len(@restriction) > 1
				set @restriction = @restriction + ', ' + convert(varchar(10), @parent)
			else
				set @restriction = @restriction + convert(varchar(10), @parent)
		END

		if exists(select * from dbo.Company where CompanyID = @parent)
			select @parent = ParentCompanyID from dbo.Company where CompanyID = @parent
		else select @parent = null
	end
	set @restriction = @restriction + ')'
	print @restriction

	if (LEN(@restriction) > 2)
	Begin
		set @stmt = 'Delete from UploadFileRevision from UploadFileRevision r
			left join WikiFileInPage p on p.CompanyID in ' + @restriction + ' AND  p.FileID=r.FileID
			left join NoteDoc n on n.CompanyID in ' + @restriction + ' AND  n.FileID=r.FileID
			where r.CompanyID = ' + convert(varchar(10), @companyID) + ' and p.PageID is null and n.NoteID is null'
		print @stmt
		exec sp_executesql @stmt
		
		set @stmt = 'Delete from UploadFileRevision from UploadFileRevision r
			left join UploadFile p on p.CompanyID in ' + @restriction + ' AND p.FileID=r.FileID
			where r.CompanyID = ' + convert(varchar(10), @companyID) +' AND r.FileRevisionId<>LastRevisionID;'
		print @stmt
		exec sp_executesql @stmt
	
		set @stmt = 'Delete from UploadFile from UploadFile f
			left join UploadFileRevision r on f.CompanyID in ' + @restriction + '  AND f.FileID=r.FileID
			where r.CompanyID = ' + convert(varchar(10), @companyID) + ' and r.FileID is null'
		print @stmt
		exec sp_executesql @stmt
	END

	fetch c into @companyID
end
close c
deallocate c