SQ代码如下:
USE [PCA]
GO
/****** Object: StoredProcedure [dbo].[PlateCheck] Script Date: 03/12/2018 14:57:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--EXEC PCA.dbo.LineInOut_Mail
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[LineInOut_Mail]
AS
BEGIN
declare
@report_name nvarchar(200),
@bodyHTML nvarchar(max)='',
@MailTo varchar(4000)='',
@MailCC varchar(4000)='',
@count varchar(10)
select @report_name=N'線上線下數據郵件測試';
SELECT @MailTo='Peng.Tao@inventec.com.cn;Wang.FeiWF@inventec.com.cn;Cheng.Shao-jie@inventec.com.cn';
-- 定義郵件內容
set @bodyHTML= N'<H2>Line_In_Out相關數據</H2>' + '<table border=1 style="text:center">'+
'<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>'+
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,''
FROM PCA..LINE_I_O A,PCA..WC B,FIS2..ACCOUNT C WHERE A.WC=B.WC AND C.Account = A.LineOut
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))+
N'</table>';
-- 定義各項數據進行郵件發送
set @bodyHTML =@bodyHTML
EXEC master.dbo.xp_smtp_sendmail
@FROM =@MailTo,
@FROM_NAME =N'PT_Test',
@TO =@MailTo,
@subject =@report_name,
@message =@bodyHTML,
@type =N'text/html',
@server=N'ics-mx03'
END