SQL代码如下:
1 USE [PCA] 2 GO 3 /****** Object: StoredProcedure [dbo].[PlateCheck] Script Date: 03/12/2018 14:57:27 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 --EXEC PCA.dbo.LineInOut_Mail 9 -- ============================================= 10 -- Author: <Author,,Name> 11 -- Create date: <Create Date,,> 12 -- Description: <Description,,> 13 -- ============================================= 14 ALTER PROCEDURE [dbo].[LineInOut_Mail] 15 16 AS 17 BEGIN 18 declare 19 @report_name nvarchar(200), 20 @bodyHTML nvarchar(max)='', 21 @MailTo varchar(4000)='', 22 @MailCC varchar(4000)='', 23 @count varchar(10) 24 25 select @report_name=N'線上線下數據郵件測試'; 26 27 SELECT @MailTo='Peng.Tao@inventec.com.cn;Wang.FeiWF@inventec.com.cn;Cheng.Shao-jie@inventec.com.cn'; 28 29 30 -- 定義郵件內容 31 set @bodyHTML= N'<H2>Line_In_Out相關數據</H2>' + '<table border=1 style="text:center">'+ 32 '<tr><td>McbSno</td><td>Model</td><td>Description</td><td>Code</td><td>Remark</td><td>Name</td><td>Out_Cdt</td><td>LineIn</td><td>In_Cdt</td><td>ID</td></tr>'+ 33 CAST( (SELECT td=A.McbSno,'',td=A.Model,'',td=B.Description,'',td=A.Code,'',td=A.Remark,'',td=C.Name,'',td=A.Out_Cdt,'',td=A.LineIn,'',td=A.In_Cdt,'',td=A.id,'' 34 FROM PCA..LINE_I_O A,PCA..WC B,FIS2..ACCOUNT C WHERE A.WC=B.WC AND C.Account = A.LineOut 35 AND A.Out_Cdt BETWEEN DateAdd(day, -1, getdate())+'07:59' AND DateAdd(day, 0, getdate())+'08:00' FOR XML PATH('tr'), TYPE )AS NVARCHAR(MAX))+ 36 N'</table>'; 37 -- 定義各項數據進行郵件發送 38 set @bodyHTML =@bodyHTML 39 EXEC master.dbo.xp_smtp_sendmail 40 @FROM =@MailTo, 41 @FROM_NAME =N'PT_Test', 42 @TO =@MailTo, 43 @subject =@report_name, 44 @message =@bodyHTML, 45 @type =N'text/html', 46 @server=N'ics-mx03' 47 END