CODEWITHSUNDEEP
sqlsql-serverstringsql-server-2008
sona
sona

Reputation: 1552

How to display data in mail in proper format from Sql server database table?

I am sending mail when a user registers to a mail Id through database.I want space between the text fields in mail body.

SQL query:

SELECT @Message =@MessageHeader + char(13) + char(10)+CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
 +'Company Name: ' +  IsNull([CompanyName],'') + char(13) + char(10)         
 + 'Contact Name:' + IsNull([ContactName],'') +char(13) +char(10) 
 + 'Contact Email:'+ CHAR(9) + CHAR(9)  + IsNull([ContactEmail],'') +char(13) +char(10) 
 + ' Password:'+ CHAR(9) + CHAR(9)  + IsNull([Password],'') +char(13) +char(10) 
 + 'Phone Number:'+ CHAR(9) + CHAR(9)  + IsNull([PhoneNumber],'') +char(13) +char(10) 
 + 'Address:'+ CHAR(9) + CHAR(9)  + IsNull([Address],'') +char(13) +char(10) 
 + 'Country:'+ CHAR(9) + CHAR(9)  + IsNull([Country],'') +char(13) +char(10) 
 + 'State:'+ CHAR(9) + CHAR(9)  + IsNull([State],'') +char(13) +char(10) 
 + 'City:'+ CHAR(9) + CHAR(9)  + IsNull([City],'') +char(13) +char(10) 
 + 'Zip:'+ CHAR(9) + CHAR(9)  + IsNull([Zipcode],'') +char(13) +char(10) 
 + 'Fax:'+ CHAR(9) + CHAR(9)  + IsNull([Fax],'') +char(13) +char(10) 

FROM MemTable WHERE MemberID= @MemberID

Now output in mail is:

Member ID:   22736
Company Name: tesCFS
Contact Name: tesCFS
Contact Email: [email protected]
Password: tesCFS
Phone Number: 213
Address:     tesCFS
Country:    
State:  
City:   
Zip:    
Fax:

I want the output as:

Member ID:          22736
Company Name:       tesCFS
Contact Name:       tesCFS
Contact Email:      [email protected]
Password:           tesCFS
Phone Number:       213
Address:            tesCFS
Country:    
State:  
City:   
Zip:    
Fax:

Upvotes: 1

Views: 818

Answers (1)

Riley Major
Riley Major

Reputation: 2014

I assume you want the two columns to line up. You could consider using the HTML email format, wrapping things up in manually constructed table row elements. It's not elegant, but it's effective.

If you want to keep it text, you will need to use a variable number of tabs depending on how long your names are, and the quantity will depend on how many spaces the display program (presumably, an email client) affords each tab character. You could instead REPLICATE spaces for absolute control. See below code examples.

In either case, you will either have to (1) manually compute and code however many tabs or spaces you need or (2) have the names themselves be returned by a table and then perform calculations on them based on their length.

SELECT

-- Option 1, using tabs and manually figuring out how many are required
'Message Header' + char(13) + char(10)+CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'Company Name:' +  CHAR(9) + IsNull(CompanyName,'') + char(13) + char(10)         
+ 'Contact Name:' + CHAR(9) + IsNull(ContactName,'') +char(13) +char(10) 
+ 'Zip:' + CHAR(9) + CHAR(9) + CHAR(9) + IsNull(Zip,'') +char(13) +char(10) AS Option1,

-- Option 2, using spaces and manually figuring out how many are required
'Message Header' + char(13) + char(10)+CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ 'Company Name:' + REPLICATE(' ',3) + IsNull(CompanyName,'') + char(13) + char(10)         
+ 'Contact Name:' + REPLICATE(' ',3) + IsNull(ContactName,'') +char(13) +char(10) 
+ 'Zip:' + REPLICATE(' ',12) + IsNull(Zip,'') +char(13) +char(10) AS Option2,

-- Option 3, using dynamic space calculation
'Message Header' + char(13) + char(10)+CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
+ CompanyNamePrefix + ':' + REPLICATE(' ',(ColNum-LEN(CompanyNamePrefix))) + IsNull(CompanyName,'') + char(13) + char(10)         
+ ContactNamePrefix + ':' + REPLICATE(' ',(ColNum-LEN(ContactNamePrefix))) + IsNull(ContactName,'') +char(13) +char(10) 
+ ZipPrefix + ':' + REPLICATE(' ',(ColNum-LEN(ZipPrefix))) + IsNull(ZipPrefix,'') +char(13) +char(10) AS Option3

FROM
(
    SELECT
        15 AS ColNum,
        'Company Name' AS CompanyNamePrefix,
        'Contact Name' AS ContactNamePrefix,
        'Zip' AS ZipPrefix
) AS Names
CROSS JOIN
(
    SELECT
         'Ed''s Company' AS CompanyName,
         'Ed' AS ContactName,
         '55120' AS Zip
    UNION ALL
    SELECT
        'Bill''s Company',
        'Bill',
        '90210'
) AS MemTable;

Upvotes: 1

Related Questions