[RESOLVED] SQL Server String Concatenation with Possible Nulls

[szlamany]

I've done this in a UDF

CREATE FUNCTION dbo.GetFullName_F (@LastName as varchar(35),@FirstName as varchar(20),@MiddleName as varchar(20),@Suffix as varchar(10),@Opt as int)
RETURNS varchar(100) AS  
BEGIN
	Declare @WN varchar(100)

	Set @WN=IsNull(@LastName,'')+', '+IsNull(@FirstName,'')
		+Case When IsNull(@MiddleName,'')<>'' Then ' '+@MiddleName Else '' End
		+Case When IsNull(@Suffix,'')<>'' Then ', '+@Suffix Else '' End

	RETURN @WN
END

[szlamany]

This won't work

tblHealthCareProviders.HCPSuffix is not null

if the field contains blanks...

[MMock]

This won't work

tblHealthCareProviders.HCPSuffix is not null

if the field contains blanks...

I cannot test that as the field does not contain blanks, but that does not mean it won't. So thanks for catching that.

[szlamany]

I only use FIELD IS NULL or FIELD IS NOT NULL when testing for date columns

Otherwise I feel safer using

IsNull(FIELD,'')<>''