philip.cullingworth (10/17/2012)
To me this feels more like a trick question than a question about coalesce. The trick was to notice that 'NULL' was quoted and not the NULL value. A valid point, but nothing to do with coalesce.My other gripe is that it doesn't actually use coalesce in a sensible manner. One of the things about coalesce is that you don't have to nest them as it will work through until it finds a non-NULL value. If the question was really about coalesce and not isnull then I think the query could (and should?) have been written as
Declare @a varchar(100)
select @a = null
select @a = coalesce('NULL','NA',@a,'Pass')+'Fail'
select @a
Philip
Very true as COALESCE function takes the first "Not NULL" value from the list of options. In case, we want to play around with the sequence, nesting of the funtion become very handy.
Furthermore, Here is the correct sequence for with the COALESCE boils down to 🙂
select @a = coalesce(@a,'NULL','NA','Pass')+'Fail'
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questions
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter