SQL拼接 html 发送 

--在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

 

posted @ 2017-06-09 14:06  yufeiyunsui  阅读(1256)  评论(0)    收藏  举报