Why does this SQL script work the way it is?

Question

I am extracting email address entries from a table in SQL Server 2005 and want to create a single row that will be used as the @recipients list with sp_send_dbmail . There is a field in the table called EmailAddress, and there are 10 entries in the table.

I'm doing it:

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

@Email now has a ten letter delimited list of 10 email addresses from the accounts table.

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

Answer 1

Because for each line, you combine the current @email value with the next result in EmailAddress . String concatenation is similar to calling a function because it must evaluate the result for each line in the sequence.

Answer 2

Let's say you have 3 addresses:

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

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

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

For the last line, @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 line.

Answer 4

Your SELECT does not select rows — for display — it combines the expression multiple times with the same variable. So at the end, all you have to show is a variable.

Presumably you will find this with a different line in the batch file, for example. "SELECT @email"

Think:

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

You might think that the expression SELECT variable = represents the purpose of the combination and SELECT; but this is really just an assignment. In any case, @email is not initialized for each line.