stored procedure for sending email with html table formatting issue

On of the previous developers had a process that sent email alerts in the form of an html table in an email, this was done by triggering an stored procedure and passing the relevant parameters. This is a little outside my skills at the moment but im trying to get the same procedure to send an email for lateness. It works fine bar the formatting and layout. the query is -

在以前的开发人员有一个过程,在电子邮件中以html表的形式发送电子邮件警报,这是通过触发存储过程并传递相关参数来完成的。这有点超出我的技能,但我试图通过相同的程序发送电子邮件为迟到。它工作正常格式和布局。查询是 -

SET @xml = CAST((   SELECT      td = '<td align = "Center">' + CONVERT(VARCHAR(12),EmployeeName) + '</td>'
                                , td = '<td align = "Center">' + CONVERT(VARCHAR(12),StartTime) + '</td>'
                                , td = '<td align = "Center">' + CONVERT(VARCHAR(12),Late) + '</td>'
                    FROM        #tempLateEmail  

                    FOR XML PATH('tr'),TYPE).value('.','NVARCHAR(MAX)'
                    ) AS NVARCHAR(MAX))

The body output is :

身体输出是:

SET @body = 
                  N'<style type="text/css">'
            + 'body {font-family: Arial;font-size:12px;} '
            + 'table{font-size:11px; border-collapse:collapse;table-layout: fixed} '
            + 'td{ border:1px solid black; padding:3px;} '
            + 'th{background-color:#F1F1F1;border:1px solid black; padding:3px;}'
            + 'h1{font-weight:bold; font-size:12pt}'
            + 'h2{font-weight:bold; font-size:10pt}'
        + '</style>'
        + '<body style="width:620px; margin:15px;">'
            + '<div style="margin:0 auto; width:300px">'
                + '<h1>Lateness Email on' + ' ' + convert(varchar, getdate(), 103) + '</h1>'
            + '</div>'
            + '<div style="float:left; width:250px">'
                + '<p>The following people appear to have been late today</p> '
            + '</div>'
            + '<div style="float:left; margin-left:20px; width:250px">'
                + '<div style="height:150px; overflow:auto">'
                + '<table width="250px">'
            + '<tr>'
            + '<th width = 100>Employee</th>'
            + '<th width =100>Expected Start Date</th>'
            + '<th width =100>Minutes Late</th>'
            + '</tr>'
                + @xml
                + '</table>'
                + '</div>'
            + '</div>'

            + '<div style="clear:both;"></div>'
        + '</body>' 

        SET @subject = 'Lateness Email for - ' + convert(varchar, getdate(), 103)

It then outputs them side by side instead of in a table. I understand why its doing it but I can not work out what to do about it. Hope i've provided enough info

然后它并排输出它们而不是表格。我明白为什么它这样做但我无法弄清楚该做些什么。希望我已经提供了足够的信息

1 个解决方案

#1

as per Rohit Tiwari's comment there was indeed a <tr> missing from the end of the top secion of code -

根据Rohit Tiwari的评论,在代码的顶部确实存在缺失 -

SET @xml = CAST((   SELECT      td = '<td align = "Center">' + CONVERT(VARCHAR(12),EmployeeName) + '</td>'
                                , td = '<td align = "Center">' + CONVERT(VARCHAR(12),StartTime) + '</td>'
                                , td = '<td align = "Center">' + CONVERT(VARCHAR(12),Late) + '</td>**<tr>** '
                    FROM        #tempLateEmail  

                    FOR XML PATH('tr'),TYPE).value('.','NVARCHAR(MAX)'
                    ) AS NVARCHAR(MAX))

highlighted the update above.

突出了上面的更新。

#1