Concat Null Strings In View

I want to concat strings such that if one string is null, it is treated as an empty string. For example 'abc' + NULL = 'abc'.

SQL Server has the CONCAT_NULL_YIELDS_NULL defaulted to ON, and it needs to be on in most cases when dealing with indices. (INSERT, DELETE, UPDATE, etc.) However, when I am running a select, and one field has a null value, I still want to see the other fields in the resulting string.

I can do this in a function, but I need a view, and the view doesn't seem to let me save a command with 'SET' in it. (However, it does let me run the command! And it works!) It's just when I try to save it, I get a syntax error, so the view is unavailable to me as I want it.

The command that works, but won't save is:

PHP Code:





 SET CONCAT_NULL_YIELDS_NULL OFF
SELECT t1.field1+'-'+'t2.field2 AS viewField
  FROM table1 t1 INNER JOIN
           table2 t2 ON t1.fk1=t2.pk2 






The tables look like this:

Table1

PHP Code:





 create table user.dbo.table1
(
  pk1 int primary key,
  fk1 int,
  field1 varchar(32),
  FOREIGN KEY fk1 REFERENCES table2 (pk2)
) 






Table2

PHP Code:





 create table user.dbo.table2
(
  pk2 int primary key,
  field2 varchar(32)
) 






table1

PHP Code:





 pk1 fk1 field1
 1     1    'test1'
 2     2    'test2'
 3     1    'test3' 






table2

PHP Code:





 pk2  field2
 1     NULL
 2     'tab2 test' 







I expect to see:

PHP Code:





 ViewField
test1-
test2-tab2 test
test3-