Sql-server – How to make variable update for each instance of for-each loop

It would appear that the responsibility of sending the email lies with the ASP.NET application not the database.

I would perform a lookup of the users email from AD within the ASP.NET application and generate the email that way.

Presumably, you want to see a single entry for each unique person/address/email/website combination. If so, try this:

SELECT (person.FirstName + ' ' + person.LastName) as FullName
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].[state], '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
FROM dbo.Person
    LEFT JOIN dbo.PersonAddress ON Person.ID = PersonAddress.Person
    LEFT JOIN dbo.[Address] ON PersonAddress.[Address] = [Address].ID
    LEFT JOIN dbo.PersonPhone ON Person.ID = PersonPhone.Person
    LEFT JOIN dbo.Phone ON PersonPhone.Person = Phone.ID
    LEFT JOIN dbo.Email WITH (NOLOCK) ON Person.ID = Email.Person
WHERE (
        isclient = 'prospect'
        or isclient = 'client'
        )
    and [Address] is not null
    and name like '%Mike%'
GROUP BY (person.FirstName + ' ' + person.LastName)
    , ISNULL(Person.isClient, '')
    , ISNULL(Person.UDF1, '')
    , ISNULL([Address].City, '')
    , ISNULL([Address].state, '')
    , PersonAddress.Person
    , PersonAddress.[Address]
    , ISNULL(Phone.PhoneNumber, 'N/A')
    , Email.Email
    , Person.Website
ORDER BY isClient asc;

The GROUP BY clause at the end ensures only a single row is returned for each unique combination of columns in the GROUP BY clause. This should prevent duplicate rows being displayed in your results.

A couple of things to note:

1. Always use the schema qualifier on the FROM clause. FROM Person should be FROM dbo.Person -> this eliminates any confusion if you introduce new schemas in the future, and prevents the query optimizer from having to look for the default schema for your user.

2. For maintainability in the future, you probably want to name columns the same regardless of which table they are in. So for instance, instead of the ID column in the People table being named ID, and it being named Person in the Address table, I'd name it PersonID in both tables. This prevents confusion (read bugs) in joins such as dbo.Person LEFT JOIN dbo.Address ON Person.ID = Address.Person.

3. Instead of naming tables like Person, they should be named after the collection of items they contain, in plural. So, Person becomes People, and Address becomes Addresses. This eliminates confusion -> does the Address table actually contain a single address or multiple addresses?

4. WITH (NOLOCK) should be avoided like the plague it is unless you fully understand the consequences of reading rows that have been modified by other transactions but not yet committed. From MSDN:

Transactions running at the READ UNCOMMITTED level do not issue shared locks to prevent other transactions from modifying data read by the current transaction. READ UNCOMMITTED transactions are also not blocked by exclusive locks that would prevent the current transaction from reading rows that have been modified but not committed by other transactions. When this option is set, it is possible to read uncommitted modifications, which are called dirty reads. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the isolation levels.

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications using either:

The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.

The SNAPSHOT isolation level.