iT邦幫忙

0

MSSQL sp_send_dbmail 無資料顯示問題

匿名 2012-03-08 14:42:193516 瀏覽

請問目前有參考下列程式設定時發送MAIL的排成
因目前無資料時HTML格式郵件內容將會顯示"空白"
如何可在無資料時在HTML格式郵件內容顯示"無符合資料"
或"不寄送郵件" 謝謝

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'<H1>Work Order Report</H1>' +
    N'<table border="1">' +
    N'<tr><th>Work Order ID</th><th>Product ID</th>' +
    N'<th>Name</th><th>Order Qty</th><th>Due Date</th>' +
    N'<th>Expected Revenue</th></tr>' +
    CAST ( ( SELECT td = wo.WorkOrderID,       '',
                    td = p.ProductID, '',
                    td = p.Name, '',
                    td = wo.OrderQty, '',
                    td = wo.DueDate, '',
                    td = (p.ListPrice - p.StandardCost) * wo.OrderQty
              FROM AdventureWorks2008R2.Production.WorkOrder as wo
              JOIN AdventureWorks2008R2.Production.Product AS p
              ON wo.ProductID = p.ProductID
              WHERE DueDate > '2006-04-30'
                AND DATEDIFF(dd, '2006-04-30', DueDate) < 2 
              ORDER BY DueDate ASC,
                       (p.ListPrice - p.StandardCost) * wo.OrderQty DESC
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'</table>' ;

EXEC msdb.dbo.sp_send_dbmail @recipients='danw@Adventure-Works.com',
    @subject = 'Work Order List',
    @body = @tableHTML,
    @body_format = 'HTML' ;

2 個回答

2
kaowoei
iT邦研究生 4 級 ‧ 2012-03-22 14:57:46
最佳解答

改用SSIS排程寄發MAIL會比較簡單

2
Albert
iT邦高手 1 級 ‧ 2012-03-08 17:39:32

最好不要繼續蒙面

Albert iT邦高手 1 級 ‧ 2012-03-26 10:23:10 檢舉

最好不要繼續蒙面

我要發表回答

立即登入回答