DECLARE @SQL as varchar(4000)
    , @RowNum as smallint
    , @CUID as varchar(55)
    , @FullName as varchar(25)
    , @EmailID as smallint
    , @UserID as smallint
    , @IsThere as smallint
    , @Emails as varchar(200)
    , @BodyText as varchar(4000)
    , @SubjectText as varchar(1000)
    , @FileTag as varchar(1000)
    , @Count as smallint
    , @Subjects as varchar(4000)
    , @CUID2 as varchar(55)

DELETE FROM CTS.dbo.EMAIL_ARRAY

UPDATE CTS.dbo.EMAIL_FILES
SET FILETAG = 'added to'
WHERE FILEID = 14

SET @SQL = 'INSERT INTO CTS.dbo.EMAIL_ARRAY VALUES( '+CHAR(39)+'Testing'+CHAR(39)+', '+CHAR(39)+''+CHAR(39)+', 1 )'
EXEC( @SQL )
SET @SQL = 'INSERT INTO CTS.dbo.EMAIL_ARRAY VALUES( '+CHAR(39)+'Tested'+CHAR(39)+', '+CHAR(39)+''+CHAR(39)+', 3 )'
EXEC( @SQL )
SET @SQL = 'INSERT INTO CTS.dbo.EMAIL_ARRAY VALUES( '+CHAR(39)+'Testing'+CHAR(39)+', '+CHAR(39)+''+CHAR(39)+', 2 )'
EXEC( @SQL )

DECLARE EMAILY_ARRAY cursor for
    SELECT CUID
        , FULL_NAME
        , EMAIL_ID
    FROM CTS.dbo.EMAIL_ARRAY
    ORDER BY CUID

SET @RowNum = 0
SET @CUID = ''
SET @FullName = ''
SET @EmailID = 0
SET @UserID = 0
SET @IsThere = 0
SET @SQL = ''
SET @Emails = ''

OPEN EMAILY_ARRAY
FETCH NEXT FROM EMAILY_ARRAY
INTO @CUID, @FullName, @EmailID

WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @IsThere = max( CASE WHEN LEN(FULL_NAME) > 0 THEN 2 ELSE 1 END )
        , @UserID = max( USER_ID )
    FROM CTS.dbo.EMAIL_RECIPIENTS
    WHERE upper(CUID) = upper(@CUID)
    
    IF @IsThere IS NULL
    BEGIN
        SELECT @UserID = max( USER_ID )
        FROM CTS.dbo.EMAIL_RECIPIENTS

        SET @UserID = @UserID + 1
        
        SET @SQL = 'INSERT INTO EMAIL_RECIPIENTS(USER_ID, CUID, FULL_NAME) VALUES( '+cast( @UserID as varchar(5) ) +', '+CHAR(39)++@CUID++CHAR(39)+', '+CHAR(39)++@FullName++CHAR(39)+' ) '
        EXEC( @SQL )
     END
     ELSE
     BEGIN
        IF @IsThere = 1 AND LEN(@FullName) > 0
        BEGIN
            SET @SQL = 'UPDATE EMAIL_RECIPIENTS
            SET FULL_NAME = '+CHAR(39)++@FullName++CHAR(39)+'
            WHERE upper(CUID) = upper('+CHAR(39)++@CUID++CHAR(39)+')'
            EXEC( @SQL )
        END     
    END
    SELECT @IsThere = max( EMAIL_ID )
    FROM CTS.dbo.EMAIL_DISTRO
    WHERE USER_ID = @UserID
        AND EMAIL_ID = @EmailID

    IF @IsThere IS NULL
    BEGIN
        SET @SQL = 'INSERT INTO CTS.dbo.EMAIL_DISTRO VALUES( '+cast( @UserID as varchar(5) ) +', '+cast( @EmailID as varchar(5) )+' )'
        EXEC( @SQL )
        
        IF @Emails <> ''
        BEGIN
            SET @Emails = @Emails + ','
        END
    
        SET @Emails = @Emails + cast(  @EmailID as varchar(3) )
    END

SET @CUID2 = @CUID

    FETCH NEXT FROM EMAILY_ARRAY
    INTO @CUID, @FullName, @EmailID

    IF @CUID <> @CUID2
    BEGIN
        SET @SQL = ''
        SET @SQL = 'DECLARE SUBJECT_ARRAY cursor for 
        SELECT e.SUBJECT 
        FROM CTS.dbo.EMAIL e
            INNER JOIN CTS.dbo.EMAIL_DISTRO d ON e.EMAIL_ID=d.EMAIL_ID
        WHERE e.EMAIL_ID IN ( ' + @Emails + ' )
            AND d.USER_ID = ' + cast( @UserID as varchar(3) )

        IF @Emails <> ''
        BEGIN
            EXEC( @SQL ) 

            OPEN SUBJECT_ARRAY
            FETCH NEXT FROM SUBJECT_ARRAY
            INTO @SubjectText

            SET @Subjects = ''

            WHILE @@FETCH_STATUS = 0
            BEGIN
                SET @Subjects = @Subjects + '<li>' + @SubjectText + '</li>'

                FETCH NEXT FROM SUBJECT_ARRAY
                INTO @SubjectText     
            END

            CLOSE SUBJECT_ARRAY
            DEALLOCATE SUBJECT_ARRAY
            PRINT'TST'
        END
        SET @Emails = ''
    END

END

CLOSE EMAILY_ARRAY
DEALLOCATE EMAILY_ARRAY