use DB_GZFSB2016;

DECLARE @tableHTML NVARCHAR(MAX) ;

DECLARE @Subject VARCHAR(8000) ;

SET @Subject = convert(varchar(10),getdate() - 1,120) +' 作废订单';

SET @tableHTML = N'<style class="fox_global_style"> div.fox_html_content { line-height: 1.5;} /* 一些默认样式 */ blockquote { margin-Top: 0px; margin-Bottom: 0px; margin-Left: 0.5em } ol, ul { margin-Top: 0px; margin-Bottom: 0px; list-style-position: inside; } p { margin-Top: 0px; margin-Bottom: 0px } </style> <div><span id="_FoxCURSOR"></span><br></div> <div><br></div><hr id="FMSigSeperator" style="width: 210px; height: 1px;" color="#b5c4df" size="1" align="left"> <div><span id="_FoxFROMNAME"><div style="MARGIN: 10px; FONT-FAMILY: verdana; FONT-SIZE: 10pt"><div>bing.tang@fullshare.biz</div></div></span></div>'+N'<H1>作废订单数据</H1><table border="1">' +

N'<tr><th>网上订单号</th><th>本地订单号</th><th>商品编码</th><th>订单类型</th><th>订单处理状态</th></tr>' +

CAST ( (

select b.site_order_id as 'td','',b.order_id as 'td','',b.prd_no as 'td','',CAST(CASE WHEN order_user='admin' THEN '线上订单' ELSE '手工单' END AS CHAR) as 'td','','作废订单' as 'td'

from Tao_Orders a,Tao_Orders_Products b where a.order_id=b.order_id and

((order_user='admin' and b.pro_order_status=5) or

(order_user <> 'admin' and (a.site_pay_date is null or b.pro_order_status=5)))

and DateDiff(dd,a.Createdate,getdate())=1

FOR XML PATH('tr'), ELEMENTS-- TYPE

) AS NVARCHAR(MAX) ) + N'</table>';

EXEC msdb.dbo.sp_send_dbmail

@profile_name = 'fsb', --配置文件名称（也就是之前在邮件配置那的名称）

@recipients = 'bing.tang@fullshare.biz', --收件email地址

@subject = @Subject, --邮件标题

@body_format = 'HTML',  --设定格式为html

@body = @tableHTML    --邮件内容