SQL: Combine First Name and Last Name columns but if Null change to ‘Name Not Provided’

I’m currently building a simple view and need to combine both the First Name and Last Name columns to create a new Customer column. If a First Name and Last Name are not provided I’d like to change this new combined value to ‘Name Not Provided’.

Currently I use a simple select statement: LastName + ', ' + FirstName AS Customer
which appears to work fine for combing the data but if the data doesn’t exist, it will just return ', '. How do I go about changing this so it returns ‘Name Not Provided’?

Answer

SELECT Customer = CASE WHEN FirstName IS NULL AND LastName IS NULL
                       THEN 'Name Not Provided'
                       WHEN FirstName IS NULL AND LastName IS NOT NULL
                       THEN LastName
                       WHEN FirstName IS NOT NULL AND LastName IS NULL
                       THEN FirstName
                  ELSE LastName + ', ' + FirstName END
FROM dbo.TableName

Demo