USP_GET_CONSTITUENT_APPEALEMAILSTATUS_FORGIVENMARKETINGSEGMENT

create procedure dbo.USP_GET_CONSTITUENT_APPEALEMAILSTATUS_FORGIVENMARKETINGSEGMENT
( @CONSTITUENTID uniqueidentifier = null,
    @MKTSEGMENTATIONID as uniqueidentifier = null,
    @ISNOTSENT bit = null output,
    @EMAILID int = null output,
    @BOUNCE bit = null output,
    @SPAMCOMPLAINT bit = 0 output,
    @ERRORMESSAGE nvarchar(1000) = null output,
    @EMAILADDRESS nvarchar(250) = null output,
    @OPTEDOUT bit = null output
)
as
begin

declare @TBLNAME as nvarchar(100);

         --Pick first record to get first email's status of this recipient status

         select top 1 @TBLNAME = BPS.TABLENAME
         from dbo.[MKTSEGMENTATION] MS
                    inner join  dbo.[MKTSEGMENTATIONEXPORTPROCESS] as MKTSEP on MKTSEP.[SEGMENTATIONID] = MS.ID
                    inner join  dbo.MKTSEGMENTATIONEXPORTPROCESSSTATUS as MKTSEXPS on MKTSEXPS.PARAMETERSETID = MKTSEP.ID
                    inner join  dbo.BUSINESSPROCESSOUTPUT as BPS on BPS.BUSINESSPROCESSSTATUSID = MKTSEXPS.ID
            where
                    (BPS.[TABLEKEY]) = upper(convert(nvarchar(36), MKTSEXPS.ID)) and MS.ID = @MKTSEGMENTATIONID order by BPS.DATEADDED 

            if(len(@TBLNAME)>0 and exists (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TBLNAME and COLUMN_NAME = 'STATUS' ))
            begin            
                    declare @sql as nvarchar(1000) = 'select @result = case 
                                                                                                                                    when STATUS is null then 0 
                                                                                                                                    when STATUS.exist(''/STATUS/ITEM/NETCOMMUNITYEMAILID'') =1 then STATUS.value(''(/STATUS/ITEM/NETCOMMUNITYEMAILID)[1]'', ''int'') 
                                                                                                                            else 0 end 
                                                                                                                                    from dbo.['+@TBLNAME+']';

                    exec sp_executesql @SQL, N'@result int out', @EMAILID out
                    if(@EmailID >0)
                    begin
                        select @ISNOTSENT = case when (BOUNCED =1 or SPAMCOMPLAINT =1) then 1 else 0 end,
                            @BOUNCE = BOUNCED,
                            @SPAMCOMPLAINT = SPAMCOMPLAINT,
                            @ERRORMESSAGE = BOUNCETEXT,
                            @EMAILADDRESS = EMAILADDRESS,
                            @OPTEDOUT = OPTEDOUT
                            from dbo.NETCOMMUNITYEMAILJOBRECIPIENT where EMAILBBNCMAPID = @EmailID and CONSTITUENTID = @CONSTITUENTID;
                    end
            end

        set @ISNOTSENT= isnull(@ISNOTSENT,0);
        set @EMAILID = isnull(@EMAILID,0); 
        set @BOUNCE =isnull(@BOUNCE,0); 
        set @SPAMCOMPLAINT = isnull(@SPAMCOMPLAINT,0);
        set @EMAILADDRESS = isnull(@EMAILADDRESS, '');
        set @OPTEDOUT = isnull(@OPTEDOUT,0);
end