I don't understand how the SQL statement returns this answer

Question

DECLARE @EmployeeIds VARCHAR(200)

SELECT @EmployeeIds = ISNULL (@EmployeeIds + ',', '') + CAST(ID AS VARCHAR(5))
FROM tblEmployee
WHERE Gender = 'Male'

PRINT @EmployeeIds

Do you have any idea how we got the 1,3,4,5,6,9,10 ? I mean here how we got the answer as an array and not like this:

What are you trying to do? What do you expect it to return? – Soviut Mar 12 '17 at 23:23 — Soviut, Mar 12 '17 at 23:23

score 4 · Answer 1 · edited May 23 '17 at 11:53

How we got the answer as an array?

It isn't an array. It is a comma delimited string. It was generated through assigning to the variable @EmployeeIds multiple times. Once for each row in tblEmployee that matched the WHERE clause.

@EmployeeIds is null to begin with. The first row is encountered and has ID of 1.

  @EmployeeIds= ISNULL (@EmployeeIds + ',', '') + CAST(ID AS VARCHAR(5))
≡ @EmployeeIds= ISNULL (NULL + ',', '') + '1' /*Substituting in variable and column values*/
≡ @EmployeeIds= ISNULL (NULL, '') + '1'
≡ @EmployeeIds= '' + '1'

So @EmployeeIds is 1 after the first row.

The second row is processed and has ID of 3

  @EmployeeIds= ISNULL (@EmployeeIds + ',', '') + CAST(ID AS VARCHAR(5))
≡ @EmployeeIds= ISNULL ('1' + ',', '') + '3' /*Substituting in variable and column values*/
≡ @EmployeeIds= ('1' + ',')  + '3'
≡ @EmployeeIds= '1,3'

And so on. This is not a guaranteed approach to string concatenation. It can and does fail and is specifically warned against by Microsoft.

Some example warnings

Future versions will include a STRING_AGG function. For now the best alternative is XML PATH

I don't understand how the SQL statement returns this answer

1 Answers1