SQL DATABASE SEND MAIL EXAMPLE SQL发送邮件实例-CSDN博客

本文链接：https://blog.csdn.net/jjx168/article/details/132839093

USE [mydatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author: John Tse
-- Create date: <Create Date,,>
-- Description: <Description,,> Alert mail for power meter loss connection
-- =============================================
Alter PROCEDURE [dbo].[EMS_Mail_PowerMeterLSC]

AS
BEGIN

SET NOCOUNT ON;

declare @mcount int
set @mcount = 0
select @mcount = count(*)
FROM [myserver01].[EMS].[dbo].[PowerMeter]
where datediff(MINUTE,LastUpdateTime, getdate()) > 15
--select @mcount
if @mcount = 0
begin
--print @mcount
return
end

DECLARE @value VARCHAR(50)
DECLARE @xml NVARCHAR(MAX)
DECLARE @xml2 NVARCHAR(MAX)
DECLARE @body NVARCHAR(MAX)
Declare @MailSubject Nvarchar(100)

   SET @xml2 =CAST((
       select PMCode as 'td','',PMName as 'td','',[Current] as 'td','',[VoltageLL] as 'td','',[VoltageLN] as 'td','',[ActivePower] as 'td','',[PowerFactor] as 'td','',[AccEnergy] as 'td','',[LastUpdateTime] as 'td'
       FROM [Strzssvprd01].[EMS].[dbo].[PowerMeter] where datediff(MINUTE,LastUpdateTime, getdate()) > 15
       FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

--print @xml2

   begin
       SET @body ='<html>
       <style>
                               table.customTable {

                               background-color: #FFFFFF;
                               border-collapse: collapse;
                               border-width: 2px;
                               border-color: #7EA8F8;
                               border-style: solid;
                               color: #000000;
                               }

                               table.customTable td, table.customTable th {
                               border-width: 2px;
                               border-color: #7EA8F8;
                               border-style: solid;
                               padding: 5px;
                               }

                               table.customTable thead {
                               background-color: red;
                               color: yellow;
                               }
                               </style>
                           <body>
                               <table class="customTable">
                               <thead>
                               <th> PMCode </th>
                               <th> PMName </th>
                               <th> Current </th>
                               <th> VoltageLL </th>
                               <th> VoltageLN </th>
                               <th> ActivePower </th>
                               <th> PowerFactor </th>
                               <th> AccEnergy </th>
                               <th> LastUpdateTime </th>
                               </thead>'
       SET @body = @body + @xml2 +'</table></body></html>

       SET           @mailSubject = ' EMS Power Meters loss connection list ' +CONVERT(VARCHAR(10), DATEADD(day, DATEDIFF(day, 0, GETDATE()-1), 0) , 111)

       EXEC        msdb.dbo.sp_send_dbmail

                   @profile_name='Mail PROFILE NAME',
                   @recipients   = 'JohnT@163.com',-- @RecipientMB,
                   --@blind_copy_recipients=   'JohnT@163.com;',
                   @body = @body,
                   @body_format='HTML',
                   @subject= @mailsubject;
       end
END