SQL Server database uses stored procedures and dbmail to send mails at regular intervals

I have discussed how to configure database mail sending in the database. (Note: the database mail function is implemented based on SMTP. First, configure the SMTP function in the system. In the "Add/delete programs" Panel, "Add/delete WINDOWS components", select and double-click "IIS" or "application ", select "smtp service" and click "Next. This step is generally not required and can be configured directly)

This article provides an example that combines stored procedures and jobs to regularly send emails from the database:
1. Create a stored procedure and call the sp_send_dbmail stored procedure in the newly created Stored Procedure (Note: The \ 'in the Code is only the Escape Character automatically added when the code is added, and only single quotation marks are provided in the actual code)

Copy codeThe Code is as follows:
Create PROCEDURE [dbo]. [sp_send_error_alert]
AS
Declare
@ V_contentnvarchar (max ),
@ V_mail_tonvarchar (500 ),
@ V_bodynvarchar (max ),
@ V_titilenvarchar (100)
, @ V_br_idnvarchar (50)
, @ V_installmentsnvarchar (10)
, @ V_remarknvarchar (100)
BEGIN
SETNOCOUNTON;
Set @ v_titile = \ 'test the mail content title;
Set @ v_content = \'\';

DECLAREcursor_repaymentCURSORFOR
SELECT
Br_id
, Installments
, Remark
Fromdw_account_repaymentt
Where
Status! = 1
Andis_del = 0
Orderbyt. add_datetimedesc;

OPENcursor_repayment
FETCHNEXTFROMcursor_repaymentINTO
@ V_br_id -- ID
, @ V_installments -- number of periods
, @ V_remark -- error description
WHILE @ FETCH_STATUS = 0
BEGIN

Set @ v_content = @ v_content + \ '<tr> <td> \' + @ v_br_id + \ '</td> \' + \ '<td> \' + @ v_installments + \ '</td> \' + \ '<td> \' + @ v_remark + \ '</td> </tr> \';

Fetchnextfromcursor_repaymentinto
@ V_br_id -- ID
, @ V_installments -- number of periods
, @ V_remark -- error description
End;

CLOSEcursor_repayment; -- close the cursor
DEALLOCATEcursor_repayment; -- clear the cursor

IfLEN (@ v_content)> 0
Begin
SET @ v_body = \ 'SET @ v_body = @ v_body + @ v_content + \ '</table> </body> EXECmsdb. dbo. sp_send_dbmail
@ Recipients = N \ 'receive user address 1@qq.com; receive user address 2@qq.com \ ', @ body = @ v_body, @ body_format = \ 'html \'
, @ Subject = @ v_titile, @ profile_name = \ 'configuration file name of the previous node (db_profiler )\

Create a Job (SqlServer proxy-> Job), set execution parameters, and regularly execute this stored procedure to implement scheduled mail sending.