SQL拼接 html 发送
时间：2021-07-01 10:21:17
帮助过：10人阅读
在Job BES_Daily_FTP_filedownload 中使用
ALTER proc [dbo].
[RSP_FN_UNAPPLIED_Mail_Reminder]
as
Declare @MailAddr varchar(
max)
DECLARE @xml NVARCHAR(
MAX)
DECLARE @body NVARCHAR(
MAX)
--Generate receive person list
set @MailAddr = ‘‘
select @MailAddr=@MailAddr+s.User_Mail
+‘;‘
from Mst_UserInfo s
where User_IsValid
=1 and substring(User_IsMail,
1,
1)
=‘8‘ and User_Mail
is not null and User_Mail
!=‘‘
order by s.User_Mail
--print @MailAddr
--generate mail body
SET @xml =
CAST((
SELECT [Business_Unit] ‘td‘,
‘‘
,isnull(
[Customer_Name],remitter_name)
‘td‘,
‘‘
,[Receipt_No] ‘td‘,
‘‘
,convert(
varchar(
10),
[Receipt_Date],
120)
‘td‘,
‘‘
,convert(
varchar(
100),
cast(Receipt_Unapplied_Amount
as money),
1)
‘td‘,
‘‘ --Commas every three digits
,
convert(
varchar(
100),
cast(Receipt_Amount
as money),
1)
‘td‘,
‘‘
,[Currency_Name] ‘td‘,
‘‘
,Bank_Name ‘td‘ ,
‘‘
,[Receipt_Remark] ‘td‘
-- ,[Bank_Account]
-- ,[Customer_JDE_No]
-- ,[Receipt_Year]
-- ,[Receipt_Period]
-- ,[Remitter_Bank_Name]
-- ,[Bank_Branch]
-- ,[Bank_Account]
-- ,[Bank_Charge_Amount]
-- ,[Receipt_Type]
-- ,[Receipt_GL_Date]
-- ,[Receipt_Applied_Amount]
-- ,[Receipt_Status]
-- ,[Receipt_Status_No]
-- ,[Batch_Name]
-- ,[Batch_Status]
-- ,[Customer_Oracle_no]
-- ,[Action_Date]
-- ,[Serial_No]
-- ,[Receipt_Remark]
-- ,[IsHistory]
-- ,[Download_Date]
FROM [BankReceipt]
left join mst_bankinfo
on [Remitter_Bank_Name] = bank_id
Where receipt_status_no
in (
100,
300,
1000)
and business_unit
in (
‘pvg‘,
‘pws‘,
‘sgc‘)
and Receipt_Status
<>‘Unidentified‘
order by Receipt_No
desc
FOR XML PATH(
‘tr‘), ELEMENTS )
AS NVARCHAR(
MAX))
SET @body =‘<html><H1>Unapplied Receipt Report</H1>
<body><style type="text/css">
h1,body{font:10pt,"Arial"}
h1{font:small-caps 14pt}
table,td,th {border:1px solid #7F7F7F;border-collapse:collapse;}
table{width:100%}
th{background-color:#C0C0C0;font-weight:bold}
</style>
<table>
<tr>
<th>Entity</th>
<th>Remitter Name</th>
<th>Receipt No</th>
<th style="width:82px">Receive Date</th>
<th>Receipt Remain Amt</th>
<th>Receipt Total Amt</th>
<th>Currency</th>
<th>Remitter Bank Name</th>
<th>Receipt Remark</th>
</tr>‘
SET @body = @body + @xml +‘</table></body></html>‘
--send mail
--EXEC msdb.dbo.sp_send_dbmail
--@blind_copy_recipients = @MailAddr,
--@body = @body,
--@body_format =‘HTML‘,
--@subject =‘Unapplied Receipt Report‘
exec msdb.dbo.CL_SendSingleMail
‘‘--sendtousermailSysMail [Sys.Admin@emerson.com]
,
‘‘--cc
,
@MailAddr--@MailAddr--bcc
,
‘SysMail‘--sendername
,
‘Sys.Admin@emerson.com‘--senderaddr
,
‘Unapplied Receipt Report‘--mailsubject
,
@body--mailcontent
,
‘normal‘ --importance low/normal/high
SQL拼接 html 发送
标签：rac htm receive sendto use char -- count import
