MS SQL Server结果生成HTML表格并发送邮件
配置邮件服务器
在SQL管理器中找到“管理”，在“数据库邮件”上面右键，选择“配置数据库邮件”，如果没有开启，系统会提示开启。
进去之后配置好账户和配置文件
生成HTML表格
使用SQL的XML功能生成表格，需要格式的话可以使用Style来设置，示例：
- DECLARE @emailBody NVARCHAR(MAX);
- SET @emailBody = N'<style>table{table-layout:fixed;width:1200px;border:1px solid #000000;border-collapse:collapse;font-size:12px;empty-cells:show;}'
- + N'th,td{border:1px solid #000000;padding:3px;}</style>'
- + N'<H2>' + @cHandler + '在'
- + CONVERT(VARCHAR(19), @dnverifytime, 120)
- + '审核的采购入库单《' + @cCode
- + '》发生超采购订单收货情况</H2>' --标题
- + N'<table>' + N'<thead><tr>'
- + N'<th style="width:80px;">采购订单</th>'
- + N'<th style="width:90px;">存货编码</th>'
- + N'<th style="width:350px;">存货名称</th>'
- + N'<th style="width:150px;">规格</th>'
- + N'<th style="width:80px;">订单数量</th>'
- + N'<th style="width:80px;">已收数量</th>'
- + N'<th style="width:80px;">超收数量</th>'
- + N'<th style="width:60px;">单位</th>'
- + N'<th style="width:80px;">超收率</th>'
- + N'<th style="width:60px;">采购员</th>'
- + N'</tr></thead><tbody>' --表头
- + CAST(( SELECT poM.cPOID AS td ,
- '' ,
- inv.cInvCode AS td ,
- '' ,
- inv.cInvName AS td ,
- '' ,
- ISNULL(inv.cInvStd, ' ') td ,
- '' ,
- '<div style="text-align:right;">'
- + CAST(CAST(po.iQuantity AS DECIMAL(20,
- 4)) AS VARCHAR(20))
- + '</div>' AS td ,
- '' ,
- '<div style="text-align:right;">'
- + CAST(CAST(po.iReceivedQTY AS DECIMAL(20,
- 4)) AS VARCHAR(20))
- + '</div>' AS td ,
- '' ,
- '<div style="text-align:right;">'
- + CAST(CAST(po.iReceivedQTY
- - po.iQuantity AS DECIMAL(20,
- 4)) AS VARCHAR(20))
- + '</div>' AS td ,
- '' ,
- unit.cComUnitName AS td ,
- '' ,
- '<div style="text-align:right;">'
- + CAST(CAST(( po.iReceivedQTY
- - po.iQuantity )
- * 100 / po.iQuantity AS DECIMAL(18,
- 2)) AS VARCHAR(20))
- + '%</div>' AS td ,
- '' ,
- poM.cMaker td ,
- ''
- FROM Inserted i
- INNER JOIN dbo.rdrecords01 d ON i.ID = d.ID
- INNER JOIN PO_Podetails po ON d.iPOsID = po.ID
- INNER JOIN dbo.PO_Pomain poM ON poM.POID = po.POID
- INNER JOIN dbo.Inventory inv ON inv.cInvCode = d.cInvCode
- INNER JOIN dbo.ComputationUnit unit ON unit.cComunitCode = inv.cComUnitCode
- WHERE po.iReceivedQTY > po.iQuantity
- FOR
- XML PATH('tr') ,
- TYPE
- ) AS NVARCHAR(MAX))
- + N'</tbody></table>';
因为生成XML时会对<>等标签进行编码，所以还需要进一步替换：
- SELECT @emailBody = REPLACE(REPLACE(@emailBody,
- '<', '<'),
- '>', '>');
发邮件
一下步就是把生成的内容通过邮件发送出去了：- EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dgml',
- @recipients = @recipientsList,
- @subject = '超采购订单收货提醒!',
- @body = @emailBody, @body_format = 'html';
上面的收件人可以直接拼出来，也可以从数据库里面取出来，下面是取数据库内容，每个收件人的格式：人名<邮件地址>，多个收件人之间用;隔开
- DECLARE @recipientsList NVARCHAR(MAX); --收件人
- SELECT @recipientsList = REPLACE(REPLACE(STUFF(( SELECT
- ';'
- + u.cUser_Name
- + '<'
- + u.cUserEmail
- + '>'
- FROM
- dbo.WG_Subscription_Users su
- INNER JOIN dbo.UserHrPersonContro up ON up.cPsn_Num = su.cPsn_Num
- INNER JOIN UFSystem.dbo.UA_User u ON u.cUser_Id = up.cUser_Id
- WHERE
- su.subscriptionID = 1
- AND ISNULL(u.cUserEmail,
- '') <> ''
- FOR
- XML
- PATH('')
- ), 1, 1, ''),
- '<', '<'),
- '>', '>');