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

Because the concatenation expression is evaluated once per row.

Answer 3

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]".

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.