I would like to point out that this function could be a performance killer if used with large data sets. There are two ways to fix this.

One, instead of the function in the select list you could do this:

SELECT

Address1,

Address2,

coalesce(Address3,'Not Available') as Address3

FROM

Customers

Or you could rewrite the function like this:

DROP FUNCTION dbo.fncNotAvailableDisplay;

go

CREATE FUNCTION dbo.fncNotAvailableDisplay

(@strInputString varchar(50))

RETURNS TABLE

AS

RETURN select coalesce(@strInputString,'Not Available') as NAVDisplay

go

And then use it like this:

SELECT

Address1,

Address2,

nad.NAVDisplay as Address3

FROM

Customers

CROSS APPLY dbo.fncNotAvailableDisplay(Address3) nad