USP_EMAILJOB_CLEANUPAFTERCOMPLETED

CREATE procedure dbo.USP_EMAILJOB_CLEANUPAFTERCOMPLETED
(
    @EMAILJOBS xml --='<EmailJobs><ID>168</ID><ID>169</ID></EmailJobs>'

)
as
begin

    -- clean up merge views from completed emails

    declare @MERGEVIEWS as Table (ID uniqueidentifier, EMAILJOBID int)

    insert into @MERGEVIEWS 
    select EJ.MERGEVIEWID MERGEVIEWID, EJ.ID from dbo.EmailJob EJ
    inner join
    (
        select c.value('.','int') as ID
        from @EMAILJOBS.nodes('/EmailJobs/ID') as T(c)
    ) as COMPLETED on EJ.ID = COMPLETED.ID
    where EJ.MERGEVIEWID is not NULL

    union
    select EJ.MERGEVIEWID, EJ.ID from EmailJob EJ where EJ.STATUS=8 and EJ.MERGEVIEWID is not NULL


    declare @VIEWSQL nvarchar(max) = ''
    select @VIEWSQL = @VIEWSQL + 'DROP VIEW dbo.' + ISV.TABLE_NAME + '; ' 
    from @MERGEVIEWS V
    inner join dbo.SQLVIEWCATALOG VC on V.ID=VC.ID
    inner join INFORMATION_SCHEMA.VIEWS ISV on ISV.TABLE_NAME = VC.VIEWNAME    

    if @VIEWSQL is not NULL
    begin
        --delete infinity view metadata

        delete from dbo.SQLVIEWCATALOG where ID in (select ID from @MERGEVIEWS)

        --drop views

        exec(@VIEWSQL)

        --clean up email job mergeview data

        update dbo.EmailJob set MERGEVIEWID = NULL where ID in (select EMAILJOBID from @MERGEVIEWS)
    end


    --delete temporary metadata tables

    declare @COMPLETEDEMAILJOBS table (EMAILJOBID int, TABLENAME sysname)

    insert into @COMPLETEDEMAILJOBS  (EMAILJOBID, TABLENAME)
    select EJTMP.EMAILJOBID, CAST(EJTMP.TABLENAME as sysname) from dbo.EMAILJOBTEMPORARYMETATDATA EJTMP
    inner join
    (
        select c.value('.','int') as ID
        from @EMAILJOBS.nodes('/EmailJobs/ID') as T(c)
    ) as COMPLETED on EJTMP.EMAILJOBID = COMPLETED.ID
    inner join INFORMATION_SCHEMA.TABLES IST on IST.TABLE_NAME = EJTMP.TABLENAME

    union
    select EJTMP.EMAILJOBID, CAST(EJTMP.TABLENAME as sysname) from dbo.EMAILJOBTEMPORARYMETATDATA EJTMP
    inner join EmailJob EJ on EJTMP.EMAILJOBID=EJ.ID
    where EJ.STATUS=8

    declare @TABLESQL nvarchar(max) = ''
    select @TABLESQL = @TABLESQL + 'DROP TABLE dbo.' + EJ.TABLENAME + '; ' 
    from @COMPLETEDEMAILJOBS EJ

    if @TABLESQL is not NULL
    begin
        --delete infinity table metadata

        delete from TABLECATALOG where tablename in (select TABLENAME from @COMPLETEDEMAILJOBS)

        exec(@TABLESQL)

        --clean up processed temporary email job metadata table

        delete dbo.EMAILJOBTEMPORARYMETATDATA
        from dbo.EMAILJOBTEMPORARYMETATDATA T
        inner join @COMPLETEDEMAILJOBS EJ on EJ.EMAILJOBID=T.EMAILJOBID
    end

end