I have a stored procedure that selects details from a customer file to build a full name and full address.
The select statement interposes spaces and commas into the full address.
ALTER PROCEDURE [dbo].[sp_Name_Addr]
(isnull([FirstName],'') +' '+ ISNULL([LastName],'')) as Fullname,
(ISNULL([StreetNumber],'') + ' ' + ISNULL([Address],'')+','+ ISNULL([Suburb],'')+','+ISNULL( [PCode],'')+','+ ISNULL([State],'')+','+ ISNULL([Country],'')) as FullAddr,[ID], [Status]
Where Firstname is Not null
and Status <> 'Expired'
ORDER BY Fullname
Some of the addresses have null in street number. This means that the full address commences with a space.
Tried to put NOT isnull(streetNumber,' ') into the select statement but the NOT is not accepted.