sql发邮件 

 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

 

posted on 2016-07-08 11:35  木龙哥  阅读(292)  评论(0)    收藏  举报

导航