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.