query not returning data!

Hi! I have a sql query in stored procedure:
SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname

Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.

You can us ISNULL or COALESCE to take care the NULL value in your Salutation column before you combine it with firstname and lastname. For example:

SELECT ISNULL(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

Or

SELECT COALESCE(Salutation,'') + ' ' + FirstName + ' ' + LastName AS fullname

|||

To be precise, the query is returning a value. The value is NULL, which in SQL terms is unknown.

So, when you add (concatenate) a known value to an unknown one, what should the result be?

Unknown!

It's analogous to saying, I'm going to add $5 to the money in my pocket. How much money is in my pocket?

You don't know, because you don't know how much money is already in my pocket.

(You don't even know if there is $5 in my pocket - there might be a hole in it. )

You have to be very careful to account for NULL values in the database.

Nothing is less than, greater than, or equal to NULL. Even NULL!

|||

Thank you! It works great now. What does COALESCE mean?

Cheers!

|||

Thank you for the explanation! Make sense.