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

 USE [BES_ADV]

 GO

 /****** Object:  StoredProcedure [dbo].[RSP_FN_UNAPPLIED_Mail_Reminder]    Script Date: 2015/6/15 16:19:58 ******/

 SET ANSI_NULLS ON

 GO

 SET QUOTED_IDENTIFIER ON

 GO

  --在Job BES_Daily_FTP_filedownload 中使用

 alter proc [dbo].[RSP_FN_UNIdentify_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)='' 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'

   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>Unidentified 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:920px}

 th{background-color:#C0C0C0;font-weight:bold}

 </style>

 <table>

 <tr>

 <th>Entity</th>

 <th>Remitter Name</th>

 <th>Receipt No</th>

 <th>Receive Date</th>

 <th>Receipt Remain Amt</th>

 <th>Receipt Total Amt</th>

 <th>Currency</th>

 <th>Remitter Bank Name</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

 ,'Unidentified Receipt Report'--mailsubject

 ,@body--mailcontent

 ,'normal'  --importance low/normal/high
巴特西

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

最新文章

热门文章
