Why does this SQL script work as it does?

Question

I'm pulling email address records from a table in SQL Server 2005, and want to build a single string to use as the @recipients list with sp_send_dbmail. The table has a field called EmailAddress and there are 10 records in the table.

I'm doing this:

DECLARE @email VARCHAR(MAX)
SELECT
    @email = ISNULL(@email + '; ', '') + EmailAddress
FROM
    accounts

Now @email has a semi-delimited list of 10 email address from the accounts table.

My questions is why/how does this work? Why doesn't @email only have the last email address in the table?

Answer 1

Because for each row you concatentate the current value of @email with the next result in EmailAddress. String concatenation is just like calling a function, in that it must evaluate the result for each row in sequence.

Answer 2

Say you have 3 addresses:

[email protected]
[email protected]
[email protected]

For the first row, @email is NULL, so it becomes "" + "[email protected]", so "[email protected]".

For the second row, @email becomes "[email protected]" + "; " + "[email protected]", so "[email protected]; [email protected]".

For the last row, @email becomes "[email protected]; [email protected]" + "; " + "[email protected]", so "[email protected]; [email protected]; [email protected]".