alter Proc p_ImportFile
as
declare
    @SQL varchar(8000),         @InsertData varchar(8000),    @CreateTable varchar(300),
    @DropTable varchar(1000),   @HighID int,                  @HighID2 int,
    @CurrentEmail varchar(100), @CurrentID int,               @CurrentName varchar(100),
    @Filename varchar(5000),    @ListID int,                  @ID int,
    @Exists varchar(100),       @To varchar(100),             @Subject varchar(100),
    @Body varchar(8000),        @OwnerID int,                 @FailureList varchar(8000),
    @Count_Total int,           @Count_Succeeded int,         @Count_Failed int

Select top 1 @ID = ID from Imports where Processed = 0 Order by ID
set nocount on
WHILE @ID is not NULL
    BEGIN
        set @Filename = (Select Filename from Imports where ID = @ID)
        set @Filename = Replace(@Filename, '''', '''''')
        set @ListID = (Select ListID from Imports where ID = @ID)

        set @DropTable = 'if exists (select * from dbo.sysobjects where id = object_id(N''[_Import_Temp]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) drop table [_Import_Temp]'
        set @InsertData = 'BULK INSERT [_Import_Temp] From ''' + @Path + @Filename + ''' With (Fieldterminator='''+@Delimiter+''')'

        exec(@DropTable) --just in case it's left over from before -- remove after debug complete
        exec(@CreateTable)
        exec(@InsertData)

        update _Import_Temp set Email = ltrim(rtrim(Email))
        update _Import_Temp set Name = ltrim(rtrim(Name))

        Create table #ContentID (ID int identity(1,1), Name varchar(100), Email varchar(100), Processed bit)
        Create table #EmailErrors(ID int identity(1,1), Name varchar(100), Email varchar(100), Reason varchar(100))
        Insert into #ContentID SELECT isnull(Name,Email), Email, 0 from _Import_Temp where PATINDEX(@Pat, email) > 0 and PATINDEX(@BadPat1, email) = 0 and PATINDEX(@BadPat2, email) = 0 and PATINDEX(@BadPat3, email) = 0 and PATINDEX(@BadPat4, email) = 0 and PATINDEX(@BadPat5, email) = 0 and len( right(email, len(email) - CharIndex('@', email) ) ) <= 67
        Set @Count_Total = @@RowCount
        Insert into #EmailErrors SELECT Name, Email, 'Invalid Email Address' from _Import_Temp where not ( PATINDEX(@Pat, email) > 0 and PATINDEX(@BadPat1, email) = 0 and PATINDEX(@BadPat2, email) = 0 and PATINDEX(@BadPat3, email) = 0 and PATINDEX(@BadPat4, email) = 0 and PATINDEX(@BadPat5, email) = 0 and len( right(email, len(email) - CharIndex('@', email) ) ) <= 67 )
        exec(@DropTable)

        While exists(Select * from #ContentID where Processed = 0)
        BEGIN
            Select TOP 1 @CurrentID = ID, @CurrentEmail = Email, @CurrentName  = Name from #ContentID where Processed = 0 order by ID
            if not(@CurrentName is NULL OR @CurrentEmail is NULL)
                BEGIN
                    SET @Exists = (Select Email FROM Subscriber WHERE Email = @CurrentEmail AND ListID = @ListID)
                    IF @Exists IS NULL
                        BEGIN
                            Insert into Subscriber (Name, Email, ListID) VALUES (@CurrentName, @CurrentEmail, @ListID)
                        END
                    ELSE
                        BEGIN
                            Insert into #EmailErrors (Name, Email, Reason) VALUES (@CurrentName, @CurrentEmail, 'Already subscribed')
                        END
                END
            ELSE
                BEGIN
                    Insert into #EmailErrors (Name, Email, Reason) VALUES (@CurrentEmail, @CurrentEmail, 'Name or Email omitted')
                END
            Update #ContentID set Processed = 1 where ID = @CurrentID
        END

        Select @Count_Failed = count(*) from #EmailErrors
        Set    @Count_Succeeded = @Count_Total - @Count_Failed
        Select @FailureList = isnull(@FailureList,'') + case [Name] when [Email] then '' else [Name] + ', ' end + [Email] + ' - ' + [Reason] + '<br>' from #EmailErrors
        Select
            @Subject = Case @Count_Succeeded
                when @Count_Total then -- All imported addys successful
                    'Import successful!'
                when 0 then -- No imported addys successful
                    'Import failed'
                else
                    'Import partially successful'
                end,
            @Body = Case @Count_Succeeded
                when @Count_Total then -- All imported addys successful
                    '<br>Successfully imported ' + cast(@Count_Succeeded as varchar(10)) + ' records.'
                when 0 then -- No imported addys successful
                    '<br><br>The following ' + cast(@Count_Failed as varchar(10)) + case @Count_Failed when 1 then ' address' else ' addresses' end + ' failed during the import<br><br>'
                    + @FailureList
                else
                    '<br>Successfully imported ' + cast(@Count_Succeeded as varchar(10)) + ' records.'
                    + '<br><br>The following ' + cast(@Count_Failed as varchar(10)) + case @Count_Failed when 1 then ' address' else ' addresses' end + ' failed during the import<br><br>'
                    + @FailureList
                end

        Select @OwnerID = OwnerID from List where ID = @ListID
        Select @To = Email from Owner where ID = @OwnerID

        EXEC p_SendMail @To, @To, @Subject, @Body
        UPDATE Imports set Processed = 1 where ID = @ID
        Drop table #ContentID
        Drop table #EmailErrors
        Select top 1 @ID = ID from Imports where Processed = 0 Order by ID
    END
exec(@DropTable)

--Hey, whaddya say we delete the import file sometime?
set nocount off