USP_COMMUNICATION_MKTSEGMENTATIONACTIVATIONSTATUS_HASOUTPUT

CREATE procedure dbo.USP_COMMUNICATION_MKTSEGMENTATIONACTIVATIONSTATUS_HASOUTPUT
(
    @SEGMENTATIONACTIVATIONSTATUSID uniqueidentifier,
    @CHANNELCODE tinyint = null
)
as
begin

    -- @CHANNELCODE pertains to the channel code on the MKTPackage record

    --    0 - Mail

    --    1 - Email

    --    null - Either mail or email


    declare @HASOUTPUT bit = 0;
    declare @HASMULTIPLELETTEROUTPUT bit = 0;

    declare @CONTINUE bit = 1;
    declare @TABLENAME nvarchar(100);
    declare @SQL as nvarchar(max);

    declare OUTPUTTABLECURSOR cursor local fast_forward for
        select 
            TABLENAME
        from dbo.MKTSEGMENTATIONACTIVATEEXPORTLINK 
        inner join dbo.BUSINESSPROCESSOUTPUT 
            on MKTSEGMENTATIONACTIVATEEXPORTLINK.EXPORTPROCESSSTATUSID = BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID 
        where MKTSEGMENTATIONACTIVATEEXPORTLINK.ID = @SEGMENTATIONACTIVATIONSTATUSID 
            and upper(BUSINESSPROCESSOUTPUT.TABLEKEY) <> upper(cast(BUSINESSPROCESSOUTPUT.BUSINESSPROCESSSTATUSID as nvarchar(36)));

    open OUTPUTTABLECURSOR;
    fetch next from OUTPUTTABLECURSOR into @TABLENAME;

    while (@@fetch_status = 0) and @CONTINUE = 1
        begin
            declare @COUNT int = 0;
            declare @LETTERCOUNT int = 0;

            set @SQL = 
                'select ' + char(13) +
                '    @COUNT = count(*), ' + char(13) +
                '    @LETTERCOUNT = count(distinct [OUTPUT].PACKAGEID) ' + char(13) +
                'from dbo.' + @TABLENAME + ' [OUTPUT] ' + char(13) +
                'inner join dbo.MKTPACKAGE ' + char(13) +
                '    on [OUTPUT].PACKAGEID = MKTPACKAGE.ID ' + char(13);

            if @CHANNELCODE is not null
                set @SQL = @SQL + 'where MKTPACKAGE.CHANNELCODE = ' + cast(@CHANNELCODE as nvarchar(1));                

            exec sp_executesql @SQL, N'@COUNT int output, @LETTERCOUNT int output', @COUNT = @COUNT output, @LETTERCOUNT = @LETTERCOUNT output;

            if @COUNT > 0
                begin
                    set @HASOUTPUT = 1;

                    if @LETTERCOUNT > 1
                        set @HASMULTIPLELETTEROUTPUT = 1;

                    set @CONTINUE = 0;
                end

            fetch next from OUTPUTTABLECURSOR into @TABLENAME;
        end

        --When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long

        close OUTPUTTABLECURSOR;
        deallocate OUTPUTTABLECURSOR;

    select 
        @HASOUTPUT as HASOUTPUT,
        @HASMULTIPLELETTEROUTPUT as HASMULTIPLELETTEROUTPUT;
end