USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETSEGMENTATIONEXPORTPROCESSOUTPUT

create procedure dbo.USP_MKTMEMBERSHIPRENEWALEFFORTPROCESSSTATUS_GETSEGMENTATIONEXPORTPROCESSOUTPUT
(
    @BUSINESSPROCESSSTATUSID uniqueidentifier
)
as
begin
    declare @BUSINESSPROCESSPARAMETERSETID uniqueidentifier;
    declare @TABLENAME nvarchar(250);
    declare @SQL nvarchar(max);

    select @BUSINESSPROCESSPARAMETERSETID = [BUSINESSPROCESSPARAMETERSETID]
    from dbo.[BUSINESSPROCESSSTATUS]
    where [ID] = @BUSINESSPROCESSSTATUSID;

    select @TABLENAME = [TABLENAME] 
    from dbo.[BUSINESSPROCESSOUTPUT] 
    where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and upper([TABLEKEY]) = upper(convert(nvarchar(36), @BUSINESSPROCESSSTATUSID));

    set @SQL = 
        'select' + char(13) +
        '  (select top 1 [ID] from dbo.[BUSINESSPROCESSOUTPUT] where [BUSINESSPROCESSSTATUSID] = @BUSINESSPROCESSSTATUSID and upper([TABLEKEY]) = upper([BUSINESSPROCESSOUTPUTSUMMARY].[TABLEKEY])) as [ID],' + char(13) +      
        '  @BUSINESSPROCESSPARAMETERSETID as [PARAMETERSETID],' + char(13) +
        '  [BUSINESSPROCESSOUTPUTSUMMARY].[TABLEKEY],' + char(13) +
        '  [BUSINESSPROCESSOUTPUTSUMMARY].[QUANTITY],' + char(13) +
        '  dbo.[UFN_MKTSEGMENTATIONEXPORTPROCESSOUTPUT_SUMMARYSTATUSCODE]([BUSINESSPROCESSOUTPUTSUMMARY].[STATUS]) as [STATUSCODE],' + char(13) +
        '  convert(bit, case when exists (' + char(13) +
        '    select top 1 1' + char(13) +
        '    from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
        '    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')' + char(13) +
        '    inner join dbo.[LETTERCODE] on [LETTERCODE].[ID] = [MKTPACKAGE].[LETTERCODEID]' + char(13) +
        '    where [LETTERCODE].[WORDFILE] is not null)' + char(13) +
        '  then 1 else 0 end) as [WORDMERGEENABLED],' + char(13) +
        '  convert(bit, case when exists (' + char(13) +
        '    select top 1 1' + char(13) +
        '    from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
        '    inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')' + char(13) +
        '    where [MKTPACKAGE].[NETCOMMUNITYTEMPLATEID] > 0)' + char(13) +
        '  then 1 else 0 end) as [NETCOMMUNITYEMAILJOBENABLED],' + char(13) +
        '  convert(bit, ' + char(13) +
        '    case when exists (' + char(13) +
        '    select top 1 1' + char(13) +
        '    from [BUSINESSPROCESSOUTPUTSUMMARY].[STATUS].nodes(''/STATUS/ITEM'') T(c)' + char(13) +
        '    where T.c.value(''(NETCOMMUNITYEMAILID)[1]'', ''integer'') > 0)' + char(13) +
        '  then 1 else 0 end) as [NETCOMMUNITYEMAILJOBSTARTED],' + char(13) +
        '  (select top 1 [MKTPACKAGE].[CHANNELCODE]' + char(13) +
        '  from [BUSINESSPROCESSOUTPUTSUMMARY].[PACKAGES].nodes(''/PACKAGES/ITEM'') T(c)' + char(13) +
        '  inner join dbo.[MKTPACKAGE] on [MKTPACKAGE].[ID] = T.c.value(''(ID)[1]'', ''uniqueidentifier'')) as [CHANNELCODE]' + char(13) +
        'from dbo.[' + @TABLENAME + '] as [BUSINESSPROCESSOUTPUTSUMMARY]' + char(13) +
        'inner join dbo.[EXPORTDEFINITION] on [EXPORTDEFINITION].[ID] = [BUSINESSPROCESSOUTPUTSUMMARY].[EXPORTDEFINITIONID]' + char(13) +
        'order by [EXPORTDEFINITION].[NAME];';

    exec sp_executesql @SQL, N'@BUSINESSPROCESSPARAMETERSETID uniqueidentifier, @BUSINESSPROCESSSTATUSID uniqueidentifier', @BUSINESSPROCESSPARAMETERSETID=@BUSINESSPROCESSPARAMETERSETID, @BUSINESSPROCESSSTATUSID=@BUSINESSPROCESSSTATUSID; 

end