Search code examples
sqlsql-serversql-server-2014

Is it possible to have the "IF" on a "select" statement or using "Case"?

I have an IF statement that basically gives me concatenation of addresses if value for them is not null. as see below :

IF @Address1 IS NOT NULL
BEGIN       
    IF @Address2 IS NOT NULL SET @TotalAddress=ISNULL(@Address1, '')+CHAR(13)+@Address2
        ELSE SET @TotalAddress=@Address1;
    IF @Address3 IS NOT NULL SET @TotalAddress=ISNULL(@Address1, '')+CHAR(13)+ISNULL(@Address2, '')+CHAR(13)+ISNULL(@Address3, '')
    IF @Address4 IS NOT NULL SET @TotalAddress=ISNULL(@Address1, '')+CHAR(13)+ISNULL(@Address2, '')+CHAR(13)+ISNULL(@Address3, '')+CHAR(13)+ISNULL(@Address4, '')
END

I was wondering if I can have my code in select statement directly and grab address1,2 and 3 without creating a variable.

Solution

  • Try this using CONCAT ,NULLIF , IIF and ISNULL

    declare @address1 varchar(10)= NULL
declare @address2 varchar(10)= '39 Cox Street'
declare @address3 varchar(10)= NULL
declare @address4 varchar(10)= 'Chicago'


select
   iif(isnull(@address1,'') = '','',
   concat(@address1 + char(13),
   nullif(@address2,'') + char(13) ,
   nullif(@address3,'') + char(13),
   nullif(@address4,'') + char(13)))

    demo found here http://rextester.com/live/OMP74388