sp 数据拼接html table表转换xml，发邮件 

 1 USE [BES_ADV]
 2 GO
 3 /****** Object:  StoredProcedure [dbo].[RSP_FN_UNAPPLIED_Mail_Reminder]    Script Date: 2015/6/15 16:19:58 ******/
 4 SET ANSI_NULLS ON
 5 GO
 6 SET QUOTED_IDENTIFIER ON
 7 GO
 8  --在Job BES_Daily_FTP_filedownload 中使用   
 9 alter proc [dbo].[RSP_FN_UNIdentify_Mail_Reminder]    
10 as    
11 Declare @MailAddr varchar(max)    
12 DECLARE @xml NVARCHAR(MAX)    
13 DECLARE @body NVARCHAR(MAX)    
14     
15 --Generate receive person list    
16 set @MailAddr = ''    
17 select @MailAddr=@MailAddr+s.User_Mail+';'    
18 from Mst_UserInfo s    
19 where User_IsValid=1 and substring(User_IsMail,1,1)='8' and User_Mail is not null and User_Mail!=''    
20 order by s.User_Mail    
21 --print @MailAddr    
22 
23 --generate mail body    
24 SET @xml =    
25 CAST((    
26 SELECT [Business_Unit] 'td',''    
27       ,isnull([Customer_Name],remitter_name) 'td',''    
28    ,[Receipt_No] 'td',''    
29       ,convert(varchar(10),[Receipt_Date],120) 'td',''    
30       ,convert(varchar(100),cast(Receipt_Unapplied_Amount as money),1) 'td','' --Commas every three digits    
31       ,convert(varchar(100),cast(Receipt_Amount as money),1) 'td',''
32       ,[Currency_Name] 'td',''    
33       ,Bank_Name 'td'      
34   FROM [BankReceipt]   
35   left join mst_bankinfo on  [Remitter_Bank_Name] = bank_id  
36 Where receipt_status_no in (100,300,1000) and business_unit in ('pvg','pws','sgc')   
37      and Receipt_Status='Unidentified'  
38 order by Receipt_No desc    
39 FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))    
40   
41 SET @body ='<html><H1>Unidentified Receipt Report</H1>    
42 <body><style type="text/css">    
43 h1,body{font:10pt,"Arial"}    
44 h1{font:small-caps 14pt}    
45 table,td,th {border:1px solid #7F7F7F;border-collapse:collapse;}    
46 table{width:920px}    
47 th{background-color:#C0C0C0;font-weight:bold}    
48 </style>    
49 <table>    
50 <tr>    
51 <th>Entity</th>    
52 <th>Remitter Name</th>    
53 <th>Receipt No</th>    
54 <th>Receive Date</th>    
55 <th>Receipt Remain Amt</th>
56 <th>Receipt Total Amt</th>
57 <th>Currency</th>    
58 <th>Remitter Bank Name</th>    
59 </tr>'    
60 SET @body = @body + @xml +'</table></body></html>'    
61     
62 --send mail    
63 --EXEC msdb.dbo.sp_send_dbmail    
64 --@blind_copy_recipients = @MailAddr,    
65 --@body = @body,    
66 --@body_format ='HTML',    
67 --@subject ='Unapplied Receipt Report'    
68   
69 exec msdb.dbo.CL_SendSingleMail   
70 ''--sendtousermailSysMail [Sys.Admin@emerson.com]  
71 ,''--cc  
72 ,@MailAddr--@MailAddr--bcc  
73 ,'SysMail'--sendername  
74 ,'Sys.Admin@emerson.com'--senderaddr  
75 ,'Unidentified Receipt Report'--mailsubject  
76 ,@body--mailcontent  
77 ,'normal'  --importance low/normal/high

 

posted @ 2015-06-15 17:08  yufeiyunsui  阅读(393)  评论(0)    收藏  举报