spGetConditionalEmailPermutations

CREATE procedure dbo.spGetConditionalEmailPermutations(@EmailJobID int)
as
begin

    ----------- **** IMPORTANT **** --------------------

    ---- This SP contains a Temp table.  The SPGen Utility

    ---- cannot handle Temp tables due to a limitation in DataAdapter.FillSchema.

    ---- If you make changes to this SP and need to regen the file, you will have to

    ---- replace the temp table with a table variable. Then you can successfully regen this file.

    ---- Afterwards, you can switch back to a temp table.


    create table #Recipient  (ID uniqueidentifier, EmailAddress nvarchar(256) collate database_default  not null , PeopleID int null, ClientUserID int null)

    insert into #Recipient                                    
    select ID, EMAILADDRESS, BOSPID, USERID
    from dbo.CONDITIONALCONTENTRECIPIENT
    where EMAILJOBID = @EmailJobID

    create table #Recipient_Priorities (RecipientID uniqueidentifier, BlockID INT, Priority INT)                          
    exec spGetConditionalPermutations @EmailJobID,NULL,NULL

    create index IX_Recipient_Priorities on #Recipient_Priorities (RecipientID, BlockID, Priority)                                                     

    update dbo.CONDITIONALCONTENTRECIPIENT
    set JOBKEY = ( SELECT Cast(BlockID as varchar) + ':' + Cast(Priority as varchar) + ';'
                    FROM #Recipient_Priorities rp2
                    WHERE rp2.RecipientID = rp1.RecipientID
                    ORDER BY BlockID
                    FOR XML PATH('') )
    from dbo.CONDITIONALCONTENTRECIPIENT C
    inner join #Recipient_Priorities rp1
    on C.ID = Rp1.RecipientID

    select distinct jobkey from CONDITIONALCONTENTRECIPIENT where Emailjobid =  @EmailJobID

end