declare @last_name varchar(30) = 'Bond'

declare @first_name varchar(30) --= 'James'

set concat_null_yields_null off

select RTRIM(LTRIM(@first_name + ' ' + @last_name)) as [OFF]

set concat_null_yields_null on

select RTRIM(LTRIM(@first_name + ' ' + @last_name)) as [ON with +]

/* Solution */

select RTRIM(LTRIM(CONCAT(@first_name, ' ', @last_name))) as [ON with CONCAT]

go

/* Take care, only use CONCAT for string addition */

declare @i int = 1

declare @j int = 2

select @i + @j as [Add two integers]

select CONCAT(@i, @j) as [CONCAT two integers]

go