brad.green (2/16/2010)
In addition to the comment about the missing order by, the result of this is also dependent on the CONCAT_NULL_YIELDS_NULL option. If this option is OFF, the argument to IsNull() will NOT be null, and therefore the result would be: ", John, Mark, Bill" -- leading comma.
Good point! Was about to "complain" as well, but then saw you already caught this.
The settings-independend version would be something like this:
SELECT @MaleNames =
CASE WHEN @MaleNames IS NULL THEN '' ELSE @MaleNames + ', ' END + [name]
FROM @table
WHERE gender = 'M'
Best Regards,
Chris Büttner