sql发送邮件- html 格式
ALTER PROCEDURE dbo.sx_pro_AutoEmailContent
AS
Begin
declare @Rqty int
declare @n int
declare @m_recipients varchar(1000)
declare @m_copy_recipients varchar(1000)
declare @m_Query varchar(1000)
declare @m_Body varchar(1000)
declare @m_subject varchar(50)
declare @m_AttachAsFile int
declare @m_FileName varchar(50)
declare @m_Frequency varchar(10)
DECLARE @tableHTML NVARCHAR(MAX)
set @Rqty=(select count(*) from sx_bas_AutoEmail_List)
set @n=1
While @n<=@Rqty
begin
select
@m_recipients=recipients,
@m_copy_recipients=copy_recipients,
@m_Query=sqlstr,
@m_Body=Body,
@m_subject=subject,
@m_AttachAsFile=AttachAsFile,
@m_FileName=File_Name,
@m_Frequency=Frequency from sx_bas_AutoEmail_List WHERE ID=@n
if @m_Frequency=\’Daily\’ or (@m_Frequency=\’Monthly\’ and day(getdate())=1) or (@m_Frequency=\’Weekly\’ and DatePart(dw,getdate())=1)
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = \’DB_AutoMail\’,
@recipients = @m_recipients,
@copy_recipients =@m_copy_recipients,
@Query=@m_Query,
@body = @m_Body,
@subject =@m_subject,
@execute_query_database = \’sxquadb\’,
@attach_query_result_as_file =@m_AttachAsFile,
@query_attachment_filename=@m_FileName;
–@body_format = \’HTML\’;
end
set @n=@n+1
end
–@profile_name = \’SAEG_db_AutoMail\’,
end