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:

a@b.c
b@b.c
c@b.c

For the first row, @email is NULL, so it becomes "" + "a@b.c", so "a@b.c".

For the second row, @email becomes "a@b.c" + "; " + "b@b.c", so "a@b.c; b@b.c".

For the last row, @email becomes "a@b.c; b@b.c" + "; " + "c@b.c", so "a@b.c; b@b.c; c@b.c".

Answer 3

Because the concatenation expression is evaluated once per row.

Answer 4

Your SELECT isn't selecting rows - for display - it's repeatedly concatenating an expression to the same variable. So at the end, all you have left to show is the variable.

Presumably you find that out with another line in the batch file, e.g. "SELECT @email"

Think:

result = "" + name1
result = result + name2
result = result + name3
etc.

Possibly you're thinking that SELECT variable = expression is a combination assignment and SELECT; but it's really just an assignment. In any case, @email isn't reinitialized for each row.