Hello,
I'm trying to build an address block but in some cases Address and AddressNo are null. After every field that is not null I need a "," to be added. If the value is null (or empty string) the field and "," must be skipped.
Here's an example:
No null --> Address,AddressNo,PostalCode,Village,District
Address & AddressNo = null --> PostalCode,Village,District
Below is my code:
CREATE PROCEDURE [dbo].[GetAddress]
( @IDNO nvarchar(20),
@Username nvarchar(256))
AS
BEGIN
SET NOCOUNT ON;
WITH cteDepartment(Department)
AS
(
SELECT Department.Department
FROM Users INNER JOIN Department
ON Users.DepartmentID= Department.DepartmentId
WHERE Users.UserName=@Username
)
SELECT Department, IDNO, CONCAT(PostalStreet,',',PostalNo,',',CONVERT(NVARCHAR(10),PostCode),',',PostCommunity,',',PostDistrict) AS AddressBlock
FROM Customers CROSS JOIN cteDepartment
WHERE IDNO=@IDNO
END
Thank you in advance.