使用电子邮件正文中的多个表从SQL Server数据库发送电子邮件 [英] Send Email from SQL Server database with multiple tables in email body
问题描述
我受到以下任务的挑战。
I am challenged by the following task.
这是我的详细问题：我需要从SQL Server发送电子邮件，其中包含电子邮件正文的HTML表格格式有几张桌子。
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.
-
查询将来自数据库，如
Query will be from database like
SELECT Field1, Field2, Field3
FROM Table
WHERE DateField >= Somedate
Field3 将是分隔表的因素，因此，结果将按 Field3 值并放在不同的表格上
Field3 will be the factor to separate the tables, hence, the result will be grouped by Field3 values and put on separate tables
电子邮件内容将或多或少如下（截图已附上）
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
我但是，尝试了游标，它只会返回其中一个表（基于第一个FETCH值）。任何帮助表示赞赏
I have tried cursor, however, it will only return only one of the tables (based on the first FETCH value). Any help is appreciated
推荐答案
使用函数我在这里提供你可以这样做：
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');
- 查询会将其构建为一个大的 XHTML
--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>
嵌入< style> 添加CSS格式的节点
Embedd a <style> node to add CSS formatting
在上面提供的链接中查找更多可能性和背景...
Find further possibilities and background at the link provided above...
这篇关于使用电子邮件正文中的多个表从SQL Server数据库发送电子邮件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
![样本结果集](\"https://i.stack.imgur.com/soJr4.png\"){kind=link}