sql server 2008 r2 - Not getting the desired results in a computed column when 1 or more of the columns used have NULLS -

did quite bit of searching , found many examples of how configure computed column find nothing on this.

i have 2 columns - lastname , firstname. in instances 1 or other of these columns null.

my computed column syntax

((convert([varchar](max),[lastname],(0))+',  ')+convert([varchar](max),[firstname],(0)) 

which works great if both columns have data.
however, when 1 or other null computed column populated null.

concatenating strings null result in null, see remarks section here more detail

you should handle nulls appropriate

create table people  (   firstname varchar(50),    lastname varchar(50) not null,    fullname cast(case when firstname not null                          lastname + ', ' + firstname                          else lastname end varchar(105)) ) 

demo

arguably though, can full name without both first , last name, perhaps here null makes sense

edit: if either firstname or lastname can null you'd want instead

case when firstname null or lastname null coalesce(firstname, lastname)      else lastname + ', ' + firstname end 

demo