How To Create Html Table In Sql Server

Home
Databases
Microsoft SQL Server

Hi,

Can we format a table column to HTML in SQL?

Thanks in advance for your reply.

8 Replies

Larry Shanahan

This person is a verified professional.

Verify your account to enable IT peers to see that you are a professional.

May 29, 2018 at 12:04 UTC

Microsoft SQL Server expert

SQL Server doesn't have an HTML data type. It can store HTML data in a varchar(max) or nvarchar(max) column, but it would be a bear to do anything beyond simple manipulations.

A better question would bewhy do you want to store HTML data?

Hi Larry,

I have a crm app who save data in sql, in the crm front layout I can email the save data but it does not consider the carriage return or enter key in the body of the mail.

Larry Shanahan

This person is a verified professional.

Verify your account to enable IT peers to see that you are a professional.

May 29, 2018 at 12:30 UTC

Microsoft SQL Server expert

If you're just looking to format query results for an email, you should look at SQL Server's XML functions. They are very powerful and can be used to format results in HTML fairly easily.

By and large, however, your application should be handling message formatting, including HTML.

David1618

This person is a verified professional.

Verify your account to enable IT peers to see that you are a professional.

May 29, 2018 at 12:54 UTC

In "manually" formatting output as an HTML table, the most important thing I've found is to remember that your results might contain NULLS. If you're doing something like this...

                                                            SELECT                              '<TR><TD>'                              &                              Column1                              &                              '</TD><TD>'                              &                              Column2                              &                              '</TD></TR>'                              as                              tablebody                              FROM                              MYTABLE

is that you need to "isnull" the columns...

                                                            SELECT                              '<TR><TD>'                              &                              ISNULL                              (                              Column1                              ,                              ''                              )                              &                              '</TD><TD>'                              &                              ISNULL                              (                              Column2                              ,                              ''                              )                              &                              '</TD></TR>'                              as                              tablebody                              FROM                              MYTABLE

That was you don't get any rows dropped because you're adding strings to NULL, which yields a NULL.

Thanks for your reply I will get a try.

CrltnFsk

This person is a verified professional.

Verify your account to enable IT peers to see that you are a professional.

May 29, 2018 at 13:58 UTC

Microsoft SQL Server expert

1. Create a table that stores templates:

CREATE TABLE [dbo].[EmailTemplate](
[EmailTemplateID] [int] IDENTITY(1,1) NOT NULL,
[TemplateName] [varchar](50) NOT NULL,
[EmailBody] [nvarchar](max) NOT NULL,
[ReplyToEmailAddress] [nvarchar](max) NOT NULL,
[SubjectLine] [nvarchar](max) NULL,
[EmailHeader] [nvarchar](max) NULL,
[EmailFooter] [nvarchar](max) NULL,
[PreHeaderText] [nvarchar](max) NULL,
[BCCEmailAddress] [nvarchar](max) NULL,
[CCEmailAddress] [nvarchar](max) NULL ...

2. Enter HTML

EmailBody:

<tr>
<td bgcolor="#ffffff" class="introContainer" width="650">
<table cellpadding="0" cellspacing="0" width="100%">
<tr>
<td align="center" bgcolor="#ffffff" style="padding: 20px; text-align: left; font-family: Calibri, Arial, sans-serif; font-size: 14px; color: #666666;">
Hi <hcFirstName>,
Thank you for being a friend!
We have processed your request... ... some more text...
Sincerely, Your friends 
</td>
</tr>
</table>
</td>
</tr>

3. Create a procedure that picks the correct template and loops through your crm data. The procedure will replace tags with data from your crm table. IE <hcFirstName> would be replaced by the actual firstname in your crm table. etc...

@BodyHTML = EmailBody

@BodyHTML = (select ltrim(rtrim(replace(@BodyHTML, '<hcFirstName>', @FirstName))))

4. Use the sp_send_dbmail utility to send your email

Larry Shanahan

This person is a verified professional.

Verify your account to enable IT peers to see that you are a professional.

May 29, 2018 at 14:55 UTC

Microsoft SQL Server expert

I wouldn't even go the sp_send_dbmail route. It should be the responsibility of the CRM application to format and send emails, particularly in mail merge situations.

Just a note: you can't use nvarchar(max) for the subject. The @subject argument of sp_send_dbmail is an nvarchar(255).

Hello,

Yes it should be the responsibility of the CRM, just wanted to know if we can do it in SQL.

This topic has been locked by an administrator and is no longer open for commenting.

To continue this discussion, please ask a new question.