Concat in creating a function returns NULL in SQL Server-CodePudding

When I run the function it returns a null value even though it executes/creates the function without errors. Some values don't include a state which is why the address needs to be concatenated.

create or alter function CustomerFullAddress
(@CustomerID CHAR)
returns VARCHAR
AS
BEGIN
    DECLARE @FullAddress VARCHAR

    SELECT @FullAddress = Concat(c.Address, ' ', c.City, ', ',
      c.StateOrRegion, ' ', c.PostalCode)
    from Customers c
    where c.CustomerID=@CustomerID 

    RETURN @FullAddress
END

CodePudding user response：

Always provide a length when defining a VARCHAR.

If you don't the size is VARCHAR(1)

You should always explicitly declare the length of datatypes.

Bad Habits to Kick : Declaring VARCHAR without (length)