sp_UserImportProcess

        CREATE Procedure [dbo].[sp_UserImportProcess]
        (
            @IMPORTID int,
            @CLIENTSID int
        )
        AS
        set nocount on

        DECLARE @ErrorVar int
        DECLARE @ImpStatus tinyInt
        DECLARE @RecsProcessed int
        DECLARE @StatusText nvarchar(4000)
        DECLARE @tempTable table
        (
        UIMLID int,
        UserName nvarchar(50),
        BackofficeID integer,
        BackofficeSystemID integer,
        CurrentBOSP integer,
        ClientUsersID integer,
        BackOfficePeopleID integer
        )


        set @ImpStatus=2
        UPDATE UserImport set [Status]=3, [ImportStart]=getutcdate(), [StatusTxt]='Import currently processing'  where ID=@IMPORTID

        --Build the temp table
        INSERT INTO @tempTable ([UIMLID],[Username],[BackofficeSystemID],[BackofficeID])
            SELECT ID, UserName, BackofficeSystemID,BackofficeID FROM [dbo].[UserImportMasterList]
            where [Status] =0 and [UserImportID]=@IMPORTID

        --Get any existing backOfficeSystemPeople Ids
        UPDATE @tempTable set CurrentBOSP=BOSP.ID
        from BackOfficeSystemPeople BOSP inner join @temptable t on BOSP.BackOfficeRecordID=t.BackofficeID
            AND BOSP.BackOfficeSystemID=t.BackofficeSystemID

        begin Transaction

        --Insert into ClientUsers
        INSERT into ClientUsers(UserName, Password, Active, LastName, FirstName, EMail, InternalUser, ClientsID, IsSupervisor, IsProvisional)
        select t.username, ml.password, 1, ml.LastName, ml.FirstName, ml.Email, 0, @CLIENTSID, 0, 0
        from @tempTable t join dbo.UserImportMasterList ml on t.UIMLID=ml.ID 

        SET @ErrorVar = @@ERROR
        IF @ErrorVar <> 0 BEGIN
            set @ImpStatus=13
            set @StatusText = 'Unable to add users: ' + convert(nvarchar(200), @ErrorVar)
            goto Finis
        END

        --Get the ClientUsersID
        UPDATE @tempTable set ClientUsersID=cl.ID
            from @tempTable t inner join ClientUsers cl on cl.userName=t.UserName and ClientsID=@CLIENTSID

        --Insert into BackOfficeSystemPeople
        INSERT into BackOfficeSystemPeople(BackOfficeSystemID, BackOfficeRecordID) 
            select BackofficeSystemID, BackOfficeID from @tempTable where CurrentBOSP is null 

        SET @ErrorVar = @@ERROR
        IF @ErrorVar <> 0 BEGIN
            set @ImpStatus=13
            set @StatusText = 'Unable to add BackOfficeSystemPeople: ' + convert(nvarchar(200), @ErrorVar)
            goto Finis
        END

        --Get those IDs
        UPDATE @tempTable set BackOfficePeopleID=bosp.ID
            from @tempTable t inner join dbo.BackOfficeSystemPeople bosp on t.BackofficeID=bosp.BackofficeRecordID and  t.BackofficeSystemID=bosp.BackOfficeSystemID

        --Insert the BackOfficeSystemUsers
        INSERT into dbo.BackOfficeSystemUsers (BackofficePeopleID,ClientUsersID,[Current])
            select BackOfficePeopleID, ClientUsersID, 1 from  @tempTable

        SET @ErrorVar = @@ERROR
        IF @ErrorVar <> 0 BEGIN
            set @ImpStatus=13
            set @StatusText = 'Unable to add BackOfficeSystemUsers: ' + convert(nvarchar(200), @ErrorVar)
            goto Finis
        END

        Finis:
        if @ImpStatus=2 
            begin
                set @StatusText = 'Import successful'
                select @RecsProcessed=count(*) from @tempTable where not ClientUsersID is null and not BackOfficePeopleID is null
                if (@RecsProcessed=0) 
                    begin
                        set @ImpStatus=13
                        set @StatusText = 'Import file contains no valid records.'
                    end
                UPDATE UserImport set [Status]=@ImpStatus, [ImportEnd]=getutcdate(), [RecordsProcessed]=@RecsProcessed, [StatusTxt]=@StatusText where [ID]=@IMPORTID
                UPDATE UserImportMasterList set ClientUsersID=t.ClientUsersID from @tempTable t join dbo.UserImportMasterList ml on t.UIMLID=ml.ID 
                Commit Transaction    
            end
        else
            begin
                Rollback transaction
                UPDATE UserImport set [Status]=13, [ImportEnd]=getutcdate(),[RecordsProcessed]=0, [StatusTxt]=@StatusText where [ID]=@IMPORTID
            end