Some time ago, because of work needs, I specially studied the use of sp_send_dbmail. I found that the examples on the Internet were too unfriendly to the novice like me /(o)/~. I had a hard time to finish the project and share it with you, not to talk about theory, just practice!
The following are the actual needs:
-- =============================================
Title: List of Group Qualifications
Description 1: <1, within one year from the expiration date and expired reminders
Description 2: <2. Header [non-attachment]: company name, issuing department, certificate name, category, grade, expiration date, warning level >
Description 3: <3, Early Warning Level: Assume that the number of months from the expiration date is N. Level 1: N <= 3; Level 2: 3 < N <= 6; Level 3: N >= 6 >
Description 4: <4, Reminder: Email Reminder
-- =============================================
The parameters of sp_send_dbmail are not detailed here (I don't know ~). We don't need much in practice. We can send html-formatted mail in the following lines.
Exec dbo.sp_send_dbmail @profile_name='crm***', --Name of sender @recipients='156240***@qq.com', --Mailbox (multiple use);Separate) @body=@tableHTML, --Message subject @body_format='HTML', --Specify the format of the message, the general text can be removed directly, send html The content of the format needs to be added @subject ='Qualification expiration early warning'; -- Subject of message
The main part below is @table HTML, where we use two ways to splice html.
1. Through the sql CAST function, most of the examples on the Internet are of this kind. I don't understand them very well. I can only draw a gourd according to the gourd.
declare @tableHTML varchar(max) SET @tableHTML = N'<H1 style="text-align:center">Qualification-related information</H1>' + N'<table border="1" cellpadding="3" cellspacing="0" align="center">' + N'<tr><th width=100px" >Corporate name</th>'+ N'<th width=250px>Certification Department</th><th width=150px>Certificate name</th>'+ N'<th width=50px>category</th><th width=50px>Grade</th>'+ N'<th width=60px>Due date</th><th width=60px>Warning level</th></tr>'+ CAST ( ( select td = p.CompanyName, '',td = p.DeptName, '',td=p.Name,'', td = p.QualificationType, '',td = p.Level, '',td = p.ExpireDates, '',td=p.YJ,'' from( select CompanyName,DeptName,Name,QualificationType,Level,Convert(varchar(50),ExpireDate,111)ExpireDates, case when DATEDIFF(mm,getDate(),ExpireDate)<=3 then 'First level warning' when DATEDIFF(mm,getDate(),ExpireDate)<=6 then 'Two level early warning' else 'Three level early warning'end YJ from T_Market***_JTZZ where 12>=DATEDIFF(mm,getDate(),ExpireDate) ) p order by p.ExpireDates asc FOR XML PATH('tr'), TYPE ) AS NVARCHAR(MAX) ) + N'</table>' ; Exec dbo.sp_send_dbmail @profile_name='crm***', @recipients = '156240***@qq.com', @subject='Qualification expiration early warning', @body=@tableHTML, @body_format = 'HTML' ;
2. It is more convenient to draw html dynamically by cursor. Although it is a bit verbose, it is flexible.
BEGIN declare @tableHTML varchar(max) declare @Companyname varchar(250) --Corporate name declare @Deptname varchar(250) --Certification Department declare @Certname varchar(250) --Certificate name declare @Certtype varchar(50) --Certificate category declare @Certlevel varchar(50) --Certificate Level declare @Expirdate varchar(20) --Due time declare @Warnlevel varchar(20) --Warning level begin set @tableHTML = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">Hello!</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">The following qualifications are about to expire or have expired. Please renew them as soon as possible.</font></p></td></tr>'; --Create temporary tables#tbl_result create table #tbl_result(companyname varchar(250),deptname varchar(250),certname varchar(250),certtype varchar(50),certlevel varchar(50),expirdate varchar(20),warnlevel varchar(10)); insert into #tbl_result select CompanyName,DeptName,Name,QualificationType,Level,convert(varchar(20),ExpireDate,23) ExpireDate,case when ms<=3 then 'Class A' when ms>3 and ms<=6 then 'second level' else 'Level three' end warnlevel from ( select *,Datediff(MONTH,GETDATE(),ExpireDate) ms from T_Market***_JTZZ where ExpireDate is not null and Datediff(MONTH,GETDATE(),ExpireDate)<=12 ) res; declare @counts int; select @counts=count(*) from #tbl_result; --- Reminder list if(@counts>0) begin set @tableHTML=@tableHTML+'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);"><th style="width:100px;">Corporate name</th><th style="width:200px;">Certification Department</th><th>Certificate name</th><th style="width:60px;">category</th><th style="width:80px;">Grade</th><th style="width:100px;">Due date</th><th style="width:80px;">Warning level</th></tr>'; --Declare cursor Declare cur_cert Cursor for select companyname,deptname,certname,certtype,certlevel,expirdate,warnlevel from #tbl_result order by expirdate; --open open cur_cert --Loop and extract records Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel While (@@Fetch_Status=0) begin set @tableHTML = @tableHTML + '<tr><td align="center">'+@Companyname+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Deptname+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Certname+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Certtype+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Certlevel+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Expirdate+'</td>'; set @tableHTML = @tableHTML + '<td align="center">'+@Warnlevel+'</td></tr>'; --Continue traversing the next record Fetch Next From cur_cert Into @Companyname,@Deptname,@Certname,@Certtype,@Certlevel,@Expirdate,@Warnlevel end --Close cursor Close cur_cert --Releasing cursors Deallocate cur_cert set @tableHTML = @tableHTML + '</table></td></tr>'; end -- Send mail exec msdb.dbo.sp_send_dbmail @profile_name='crm***', @recipients='156240***@qq.com', @body=@tableHTML, @body_format='HTML', @subject ='Qualification expiration early warning'; -- Delete temporary tables(#tbl_result) if object_id('tempdb..#tbl_result') is not null begin drop table #tbl_result; end end END
No doubt the second one is very verbose, but I feel very well to understand it. The idea of cursor splicing html is very clear. The above two methods have been practiced. If you need to use them, you only need to replace the corresponding fields and data sources. Thank you for your appreciation. I hope you will forgive me for the shortcomings. My rookie bird does not need to identify.