我们的代理每5分钟运行一次以下存储过程-它Control_EmailQueue使用此proc扫描表，以查看是否有任何新的电子邮件要发送出去。

我想测试当表中输入错误的电子邮件信息时proc的行为Control_EmailQueue。以下详细介绍了两个测试及其结果。

TEST1
我添加一条记录，在所有3个字段中Control_EmailQueue都有NULL条目EmailTO/EmailCC and EmailBCC。这可以正常工作，即错误被捕获并且其中的代码CATCH被执行，所以我收到一封标题为'ERROR OCCURED DURING EMAIL CREATION'

TEST2
我向添加了一条记录Control_EmailQueue。在字段中EmailTO，输入此字符串，'me@me.co.uk; xxxxxxx@xxxxx'即第一个电子邮件地址有效，但第二个电子邮件地址 无效
。当该程序由代理运行时，会收到一封电子邮件，me@me.co.uk但半秒钟后，会收到另一封相同的电子邮件me@me.co.uk。由于未收到CATCH标题为“
email”的邮件，因此未在该测试中执行该代码'ERROR OCCURED DURING EMAIL CREATION'。

BEGIN TRY

    DECLARE @Exit TINYINT = 0
    WHILE @Exit = 0
        BEGIN

        BEGIN TRANSACTION

            DECLARE @MailIdFound INT =
            (
            SELECT 
                    CASE 
                            WHEN MIN([EmailId]) IS NULL THEN 0
                            ELSE MIN([EmailId])
                    END
            FROM [xxx].[console].[Control_EmailQueue]
            WHERE
                    [DateInsertKey] IS NOT NULL 
                    AND
                        ( --the following gives option to re-run past mails by updating DateEmailKey to NULL
                        [DateEmailKey] IS NULL
                        OR
                        [DateEmailKey] < [DateInsertKey]
                        )
                    AND 
                    ErrorOccured = 0
                    AND 
                    EmailActive = 1
            )

            IF @MailIdFound = 0 
            BEGIN SET @Exit = 1 END --exit here as  
            ELSE

            BEGIN --send the mail here

                    --DECLARE @EmailId INT
                    DECLARE @DateInsertKey INT
                    DECLARE @DateEmailKey INT
                    DECLARE @CallingReportName NVARCHAR(1000)
                    DECLARE @EmailBCC  NVARCHAR(1000)
                    DECLARE @EmailTO  NVARCHAR(1000)
                    DECLARE @EmailCC NVARCHAR(1000)
                    DECLARE @EmailBody NVARCHAR(MAX)
                    DECLARE @EmailAttachmentPath NVARCHAR(1000)
                    DECLARE @EmailImportance VARCHAR(6)
                    DECLARE @EmailSubject NVARCHAR(1000)

                    ;WITH myMostUrgentMail_cte
                    AS
                            (
                            SELECT 
                                    TOP 1
                                    --[EmailId],
                                    [DateInsertKey],
                                    [DateEmailKey],
                                    [CallingReportName],
                                    [EmailBCC],
                                    [EmailTO],
                                    [EmailCC],
                                    [EmailBody],
                                    [EmailAttachmentPath],
                                    [EmailImportance],
                                    [EmailSubject]
                            FROM [xxx].[console].[Control_EmailQueue]
                            WHERE [EmailId] = @MailIdFound
                            )
                    SELECT 
                            @DateInsertKey          = [DateInsertKey],
                            @DateEmailKey           = [DateEmailKey],
                            @CallingReportName = [CallingReportName],
                            @EmailTO                    = [EmailTO],        
                            @EmailCC                    = [EmailCC],                        
                            @EmailBCC               = [EmailBCC],
                            @EmailBody              = [EmailBody],
                            @EmailAttachmentPath = [EmailAttachmentPath],
                            @EmailImportance        = CASE 
                                                                                WHEN [EmailImportance] = 0 THEN 'Low'
                                                                                WHEN [EmailImportance] = 1 THEN 'Normal'
                                                                                WHEN [EmailImportance] = 2 THEN 'High'
                                                                    END,
                            @EmailSubject           = [EmailSubject]
                    FROM myMostUrgentMail_cte


                    SET @EmailBody = @EmailBody + '<b>Please contact us with any questions</b></p></span></html>'
                    EXEC msdb..sp_send_dbmail
                            @recipients                     = @EmailTO,  
                            @copy_recipients            = @EmailCC,
                            @blind_copy_recipients  = @EmailBCC,
                            @subject                            = @EmailSubject,
                            @file_attachments          = @EmailAttachmentPath,
                            @Importance                 = @EmailImportance,
                            @body_format                    = 'html',
                            @body                               = @EmailBody

                    UPDATE x
                    SET 
                                x.[DateEmailKey]        = (CONVERT(CHAR(8),GETDATE(),(112))),
                                x.[DateEmailTime]   = (CONVERT([time](7),left(CONVERT([char](12),GETDATE(),(114)),(8)),(0)))
                    FROM [xxx].[console].[Control_EmailQueue] x
                    WHERE x.[EmailId] = @MailIdFound

            END

        COMMIT TRANSACTION

        END

END TRY



BEGIN CATCH

     IF @@trancount>0 
        BEGIN
                ROLLBACK TRANSACTION
        END

    -- handle error here
    DECLARE @ErrorMessage VARCHAR(100) =  '<html><p>Error occured during creation of EmailId: ' + CONVERT(VARCHAR(10),@MailIdFound) + '</p><p>xxx.console.Control_EmailQueue</p></html>'
    EXEC msdb..sp_send_dbmail
            @recipients = 'me@me.co.uk;'
            , @subject = 'ERROR OCCURED DURING EMAIL CREATION'
            , @body_format = 'html'
            , @body = @ErrorMessage

    UPDATE x
    SET x.ErrorOccured = 1
    FROM [xxx].[console].[Control_EmailQueue] x
    WHERE x.[EmailId] = @MailIdFound

END CATCH;
END