Send Email from SQL Server database with multiple

2019-08-06 12:18发布

站内问答 / SQL-Server
1687 1 4

I am challenged by the following task.

Here is my question in detail: I need to send email from SQL Server with HTML table format that the email body will have several tables.

  1. Query will be from database like 

    SELECT Field1, Field2, Field3 
FROM Table 
WHERE DateField >= Somedate

  2. Field3 will be the factor to separate the tables, hence, the result will be grouped by Field3 values and put on separate tables

  3. The email content will be more or less like the following (screenshot is attached)

    Section: Field3 Value1      
Field   Field2  Field3
   1    AA      Value1
   2    BB      Value1
   3    CC      Value1

Section: Field3 Value2      
Field   Field2  Field3
   1    OO      Value2
   2    XX      Value2
   3    VV      Value2

Section: Field3 Value3      
Field   Field2  Field3
   1    qwqw    Value3
   2    GGGG    Value3
   3    COCO    Value3

I have tried cursor, however, it will only return only one of the tables (based on the first FETCH value). Any help is appreciated

sample result set

1条回答
仙女界的扛把子
2楼-- · 2019-08-06 12:37

Using the function I provide here you can do this:

DECLARE @tbl TABLE(Field1 INT, Field2 VARCHAR(10), Field3 VARCHAR(10));
INSERT INTO @tbl VALUES
 (1,'AA','Value1')
,(2,'BB','Value1')
,(3,'CC','Value1')
,(1,'OO','Value2')
,(2,'XX','Value2')
,(3,'VV','Value2')
,(1,'qwqw','Value3')
,(2,'GGGG','Value3')
,(3,'COCO','Value3');

--The query will build this as one big XHTML

SELECT (SELECT N'Section: Field3="Value1"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value1' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )
,(SELECT N'Section: Field3="Value2"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value2' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )
,(SELECT N'Section: Field3="Value3"' AS p FOR XML PATH(''),TYPE)
,dbo.CreateHTMLTable
        (
        (SELECT * FROM @tbl WHERE Field3='Value3' FOR XML PATH('row'), ELEMENTS XSINIL)
        ,NULL,NULL,NULL
        )  
FOR XML PATH('body'),ROOT('html');

This is the result (click run code snippet to see the result formatted)

<html>
  <body>
    <p>Section: Field3="Value1"</p>
    <table>
      <thead>
        <tr>
          <th>Field1</th>
          <th>Field2</th>
          <th>Field3</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>1</td>
          <td>AA</td>
          <td>Value1</td>
        </tr>
        <tr>
          <td>2</td>
          <td>BB</td>
          <td>Value1</td>
        </tr>
        <tr>
          <td>3</td>
          <td>CC</td>
          <td>Value1</td>
        </tr>
      </tbody>
    </table>
    <p>Section: Field3="Value2"</p>
    <table>
      <thead>
        <tr>
          <th>Field1</th>
          <th>Field2</th>
          <th>Field3</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>1</td>
          <td>OO</td>
          <td>Value2</td>
        </tr>
        <tr>
          <td>2</td>
          <td>XX</td>
          <td>Value2</td>
        </tr>
        <tr>
          <td>3</td>
          <td>VV</td>
          <td>Value2</td>
        </tr>
      </tbody>
    </table>
    <p>Section: Field3="Value3"</p>
    <table>
      <thead>
        <tr>
          <th>Field1</th>
          <th>Field2</th>
          <th>Field3</th>
        </tr>
      </thead>
      <tbody>
        <tr>
          <td>1</td>
          <td>qwqw</td>
          <td>Value3</td>
        </tr>
        <tr>
          <td>2</td>
          <td>GGGG</td>
          <td>Value3</td>
        </tr>
        <tr>
          <td>3</td>
          <td>COCO</td>
          <td>Value3</td>
        </tr>
      </tbody>
    </table>
  </body>
</html>

Embedd a <style> node to add CSS formatting

Find further possibilities and background at the link provided above...

查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(4)