1 DROP PROCEDURE USP_CheckProductCodeRepeatAndSendMail
2 go
3 ---检查商家是否有重复的商品编号，如果有则发送给系统配置中接收的用户邮箱
4 CREATE PROCEDURE USP_CheckProductCodeRepeatAndSendMail
5 AS
6 BEGIN
7 IF EXISTS ( SELECT OrganizationId ,
8 ProductCode ,
9 COUNT(1) num
10 FROM dbo.T_Product
11 GROUP BY OrganizationId ,
12 ProductCode
13 HAVING COUNT(1) > 1 )
14 BEGIN
15
16 DECLARE @recipients VARCHAR(MAX)
17
18 SELECT TOP 1
19 @recipients = [NodeName]
20 FROM [dbo].[T_DicConfig]
21 WHERE NodeCode = 'CheckProductCodeRepeatAndSendMail'
22
23 IF @recipients IS NULL
24 SET @recipients = '***@qq.com'
25
26 PRINT @recipients
27
28 EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mail', -- sysname
29 @recipients = @recipients, -- varchar(max)
30 @copy_recipients = '', -- varchar(max)
31 @blind_copy_recipients = '', -- varchar(max)
32 @subject = N'商家有重复的商品编号', -- nvarchar(255)
33 @body = N'', -- nvarchar(max)
34 @body_format = 'text', -- varchar(20)
35 @importance = 'NORMAL', -- varchar(6)
36 @sensitivity = 'NORMAL', -- varchar(12)
37 @file_attachments = N'', -- nvarchar(max)
38 @query = N'SELECT *', -- nvarchar(max)
39 @execute_query_database = '***', -- sysname
40 @attach_query_result_as_file = 0, -- bit
41 @query_attachment_filename = N'', -- nvarchar(260)
42 @query_result_header = 1, -- bit
43 -- @query_result_width = 10, -- int
44 @query_result_separator = '|', -- char(1)
45 @exclude_query_output = 1, -- bit
46 @append_query_error = 1, -- bit
47 @query_no_truncate = 0, -- bit
48 @query_result_no_padding = 1, -- bit
49 @mailitem_id = 0, -- int
50 @from_address = '', -- varchar(max)
51 @reply_to = '' -- varchar(max)
52 PRINT 'sp_send_dbmail'
53 END
54 ELSE
55 PRINT 'empty data'
56 END
