SP concatenate or not?

[warway]

I am trying to make an address block from
Company eg Freds
Address1 eg 1 New Road
Address1
Town eg London
Region/County
Country Postcode eg UK EC1 4ZZ

using as store procedure shown below, but if there are two or more fields
empty the CompanyInfo 'block' is blank.

Is there a way achieve the desired reults without entering every
permuatation of the fields?

Alter PROCEDURE dbo.sp_CompanyAddress

AS

SELECT CompanyID,Company,MainContact,Telephone,Fax,Notes, 'CompanyInfo' =

CASE

WHEN Address1 IS NULL THEN Company + (Char(13) + Char(10)) + Address2 +
(Char(13) + Char(10)) + Town + (Char(13) + Char(10)) + Region + (Char(13) +
Char(10)) + Country + ', ' + PostCode

WHEN Address1 IS NULL AND Town IS NULL THEN Company + (Char(13) + Char(10))
+ Address2 + (Char(13) + Char(10)) + Region + (Char(13) + Char(10)) +
Country + ', ' + PostCode

WHEN Address1 IS NULL AND Region IS Null THEN Company + (Char(13) +
Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))+
Country + ', ' + PostCode

WHEN Address1 IS NULL AND Country IS NULL THEN Country + (Char(13) +
Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))
+ Region + (Char(13) + Char(10)) + PostCode

WHEN Address1 IS NULL AND PostCode IS NULL THEN Company + (Char(13) +
Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))
+ Region + (Char(13) + Char(10)) + Country

WHEN Address2 IS NULL THEN Company + (Char(13) + Char(10)) + Address1 +
(Char(13) + Char(10)) + Town + (Char(13) + Char(10)) + Region + (Char(13) +
Char(10)) + Country + ', ' + PostCode

WHEN Address2 IS NULL AND Town IS NULL THEN Company + (Char(13) + Char(10))
+ Address1 + (Char(13) + Char(10)) + Region + (Char(13) + Char(10)) +
Country + ', ' + PostCode

WHEN Address2 IS NULL AND Region IS Null THEN Company + (Char(13) +
Char(10)) + Address1 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))+
Country + ', ' + PostCode

WHEN Address2 IS NULL AND Country IS NULL THEN Country + (Char(13) +
Char(10)) + Address1 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))
+ Region + (Char(13) + Char(10)) + PostCode

WHEN Address2 IS NULL AND PostCode IS NULL THEN Company + (Char(13) +
Char(10)) + Address1 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))
+ Region + (Char(13) + Char(10)) + Country

WHEN Town IS NULL THEN Company + (Char(13) + Char(10)) + Address1 +
(Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Region +
(Char(13) + Char(10)) + Country + ', ' + PostCode

WHEN Town IS NULL AND Region IS Null THEN Company + (Char(13) + Char(10)) +
Address1 + (Char(13) + Char(10)) + Address2 + (Char(13) + Char(10))+ Country
+ ', ' + PostCode

WHEN Town IS NULL AND Country IS NULL THEN Country + (Char(13) + Char(10)) +
Address1 + (Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Region
+ (Char(13) + Char(10)) + PostCode

WHEN Town IS NULL AND PostCode IS NULL THEN Company + (Char(13) + Char(10))
+ Address1 + (Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) +
Region + (Char(13) + Char(10)) + Country

WHEN Region IS NULL THEN Company + (Char(13) + Char(10)) + Address1 +
(Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13)
+ Char(10)) + Country + ', ' + PostCode

WHEN Region IS NULL AND Country IS NULL THEN Country + (Char(13) + Char(10))
+ Address1 + (Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Town
+ (Char(13) + Char(10)) + PostCode

WHEN Region IS NULL AND PostCode IS NULL THEN Company + (Char(13) +
Char(10)) + Address1 + (Char(13) + Char(10)) + Address2 + (Char(13) +
Char(10)) + Town + (Char(13) + Char(10)) + Country

WHEN Country IS NULL THEN Company + (Char(13) + Char(10)) + Address1 +
(Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13)
+ Char(10)) + Region + (Char(13) + Char(10)) + PostCode

WHEN Country IS NULL AND PostCode IS NULL THEN Company + (Char(13) +
Char(10)) + Address1 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10))
+ Region + (Char(13) + Char(10))

WHEN PostCode IS NULL THEN Company + (Char(13) + Char(10)) + Address1 +
(Char(13) + Char(10)) + Address2 + (Char(13) + Char(10)) + Town + (Char(13)
+ Char(10)) + Region + (Char(13) + Char(10)) + Country

ELSE Company + (Char(13) + Char(10)) + Address1 + (Char(13) + Char(10)) +
Address2 + (Char(13) + Char(10)) + Town + (Char(13) + Char(10)) + Region +
(Char(13) + Char(10)) + Country + ' ' + PostCode

END,

CAST(Company AS varchar(20)) AS 'Short Company',

Address1, Address2,Town,Region,Country,PostCode,SLACCOUNT

FROM CompanyAddressView

ORDER BY Company







Regards Warway

 

[Sylvain Lafontaine]

Yes, by using the function isNull () instead.

With SQL 2000 (or MSDE 2000), you can also design and use your own User
Defined Function (UDF) to replace complex case coding.

S. L.

 

[warway]

Thanks,

is it therefore....

WHEN Address1 IS NULL() THEN Company + (Char(13) + Char(10)) + Address2 +
(Char(13) + Char(10)) + Town + (Char(13) + Char(10)) + Region + (Char(13) +
Char(10)) + Country + ', ' + PostCode

etc?

 

[Sylvain Lafontaine]

Not exactly:

isNull (Company, '') will return the first argument, Company, if it's
not null and the second argument, the empty string '' in this exemple, when
it is. To have Char(13) + Char(10) appended only when Company is not null,
then something like isNull (Company + Char(13) + Char(10), '') will do it.

S. L.