sp_send_dbmail只是部分工作

Question

我得到了下一个查询，问题是它适用于邮件1和2，但不适用于邮件3和4（选择表中存在3、4）。有什么想法吗？[我寻求帮助，我想正确的方法是使用光标，但我没有使用光标）。

ALTER PROCEDURE [DBMAIL].[Mail_FOR_USER]
@Mail_1 NVARCHAR (100),
@Mail_2 NVARCHAR (100),
@Mail_3 NVARCHAR (100),
@Mail_4 NVARCHAR (100)
AS  
BEGIN TRY
EXEC sp_configure 'show advanced', 1; 
RECONFIGURE;
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE
DECLARE
    @ID                     INT=1,
    @RN                     INT,
    @xCol                   XML,
    @XML_Creation_Date      DATETIME ,
    @File_Path              NVARCHAR(200),
    @USER_1                 NVARCHAR(100),
    @USER_2                 NVARCHAR(100),
    @USER_3                 NVARCHAR(100),
    @USER_4                 NVARCHAR(100),
    @Recipients_List        NVARCHAR(MAX);
---============================================
WHILE @ID<= (SELECT MAX (ID) FROM Report_FOR_Users) 
BEGIN
SELECT  [ID], [RN], [xCol], [XML_Creation_Date], [File_Path], [USER_1], [USER_2], [USER_3], [USER_4]
    FROM Report_FOR_Users WHERE ID=@ID
SET @File_Path= (SELECT File_Path FROM Report_FOR_Users WHERE ID=@ID)
SET @Mail_1= CASE
                        WHEN (SELECT USER_1 FROM Report_FOR_Users WHERE ID=@ID) IS NULL THEN ' '
                        ELSE @Mail_1    
                        END
SET @Mail_2=      CASE
                        WHEN (SELECT USER_2 FROM Report_FOR_Users WHERE ID=@ID) IS NULL THEN ' '
                        ELSE @Mail_2
                        END
SET @Mail_3=      CASE
                        WHEN (SELECT USER_3 FROM Report_FOR_Users WHERE ID=@ID) IS NULL THEN ' '
                        ELSE @Mail_3
                        END
SET @Mail_4=      CASE
                        WHEN (SELECT USER_4 FROM Report_FOR_Users WHERE ID=@ID) IS NULL THEN ' '
                        ELSE @Mail_4
                        END 
SET @Recipients_List= @Mail_1+';'+@Mail_2+';'+@Mail_3+';'+@Mail_4
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SEmailProfile',
    @recipients = @Recipients_List, 
     @body = 'ola?',
    @subject = 'Hopa',
    @file_attachments =  @File_Path;

  SET @ID=@ID+1
 END

Answer 1

ALTER PROCEDURE [DBMAIL].[Mail_FOR_USER]
(
    @Mail_1 NVARCHAR(100),
    @Mail_2 NVARCHAR(100),
    @Mail_3 NVARCHAR(100),
    @Mail_4 NVARCHAR(100)
)
AS BEGIN
    SET NOCOUNT ON;
    DECLARE
          @UserID INT
        , @File_Path NVARCHAR(200)
        , @USER_1 NVARCHAR(100)
        , @USER_2 NVARCHAR(100)
        , @USER_3 NVARCHAR(100)
        , @USER_4 NVARCHAR(100)
    DECLARE cur CURSOR FAST_FORWARD READ_ONLY LOCAL FOR
        SELECT ID, File_Path
        FROM dbo.Report_FOR_Users
    OPEN cur
    FETCH NEXT FROM cur INTO @UserID, @File_Path
    WHILE @@FETCH_STATUS = 0 BEGIN
        SELECT
              @USER_1 = CASE WHEN USER_1 IS NOT NULL THEN @Mail_1 END
            , @USER_2 = CASE WHEN USER_2 IS NOT NULL THEN @Mail_2 END
            , @USER_3 = CASE WHEN USER_3 IS NOT NULL THEN @Mail_3 END
            , @USER_4 = CASE WHEN USER_4 IS NOT NULL THEN @Mail_4 END
        FROM dbo.Report_FOR_Users
        WHERE ID = @ID
        SELECT @Recipients_List = STUFF((
            SELECT ';' + val
            FROM (
                VALUES (@USER_1), (@USER_2), (@USER_3), (@USER_4)
            ) t(val)
            WHERE t.val IS NOT NULL
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
        EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'SEmailProfile',
            @recipients = @Recipients_List, 
            @body = 'ola?',
            @subject = 'Hopa',
            @file_attachments =  @File_Path

        FETCH NEXT FROM cur INTO @UserID, @File_Path
    END
    CLOSE cur
    DEALLOCATE cur
END