不存在 不能很好地处理多个列

Question

这一定是我刚刚错过的简单东西......

我有一个临时表是这样说的：

CREATE TABLE #tsa
(
    AttendeeID int,
    SurveyID int,
    TrainingAttendeeID int
)

我得到一个使用 TOP 1 的记录，内容类似于这样：

SELECT
        TOP 1
        @AttendeeID=ta.AttendeeID,
        @SurveyID=ts.SurveyID,
        @TrainingAttendeeID = ta.TrainingAttendeeID
    FROM            
        TrainingAttendee ta
    INNER JOIN
        [User] u
    ON
        u.UserID= ta.AttendeeID
    INNER JOIN
        [Training] t
    ON
        t.TrainingID = ta.TrainingID
    INNER JOIN
        [TrainingSet] ts
    ON
        ts.TrainingSetID = t.TrainingSetID
    WHERE
        ta.AttendedTraining = 1
        AND ta.ConfirmAttendedEmailOn IS NOT NULL
        --only get people who didn't fill out the survey
        AND NOT EXISTS (SELECT * FROM SurveyTaken st WHERE st.AddedByUserID = ta.AttendeeID AND st.SurveyID = ts.SurveyID)
    ORDER BY
        ta.AttendeeID,
        ts.SurveyID

一旦我得到这条记录，我就会将其存储到我的临时表中，如下所示：

--insert into our temp table
INSERT INTO #tsa(AttendeeID, SurveyID, TrainingAttendeeID) 
VALUES(@AttendeeID, @SurveyID, @TrainingAttendeeID)

然后我需要完成检查一些数据并发送电子邮件的整个过程......一旦发送了该电子邮件，我就需要选择下一条记录，不包括我以前的记录......因此，无需显示太多代码：

WHILE SomeCondition
BEGIN
  --do some thing...
  --pick up next one
--grab next one
                SELECT
                    TOP 1
                    @AttendeeID = ta.AttendeeID,
                    @SurveyID=ts.SurveyID,
                    @TrainingAttendeeID=ta.TrainingAttendeeID
                FROM            
                    TrainingAttendee ta
                INNER JOIN
                    [User] u
                ON
                    u.UserID= ta.AttendeeID
                INNER JOIN
                    [Training] t
                ON
                    t.TrainingID = ta.TrainingID
                INNER JOIN
                    [TrainingSet] ts
                ON
                    ts.TrainingSetID = t.TrainingSetID
                WHERE
                    (
                        --same where as original
                        ta.AttendedTraining = 1
                        AND (ta.ConfirmAttendedEmailOn IS NOT NULL)
                        AND (NOT EXISTS (SELECT * FROM SurveyTaken st WHERE st.AddedByUserID = ta.AttendeeID AND st.SurveyID = ts.SurveyID))
                        --adding the piece such that we compare against the temp table...
                        AND (NOT EXISTS (SELECT * FROM #tsa tempS WHERE tempS.AttendeeID = ta.AttendeeID AND tempS.SurveyID = ts.SurveyID AND tempS.TrainingAttendeeID = ta.TrainingAttendeeID))
                    )
                ORDER BY
                    ta.AttendeeID,
                    ts.SurveyID
                --insert into our temp table    
                INSERT INTO #tsa(AttendeeID, SurveyID, TrainingAttendeeID) 
                VALUES(@AttendeeID, @SurveyID, @TrainingAttendeeID)
END

请注意其中的 where 条件。我又添加了一个和...即：

--adding the piece such that we compare against the temp table...
AND (NOT EXISTS (SELECT * FROM #tSa tempS WHERE tempS.AttendeeID = ta.AttendeeID AND tempS.SurveyID = ts.SurveyID AND tempS.TrainingAttendeeID = ta.TrainingAttendeeID))

只是为了确保我没有重用我已经在临时表中处理过的记录......你会注意到我在最后也重新插入我的临时表中......

--insert into our temp table    
INSERT INTO #tsa(AttendeeID, SurveyID, TrainingAttendeeID) 
VALUES(@AttendeeID, @SurveyID, @TrainingAttendeeID)

每次我运行这个存储过程时，它都会无限地进行，所以我相信此时我的状况出了问题。 我脑子放屁了。.或者办公室里有太多的噪音。 我在这里错过了什么？ 我放置了一个打印语句，它一直在处理相同的记录......所以有些东西告诉我我的 where 子句中的最后一个条件是不正确的。

编辑

这是整个过程...我的问题是记录集中只有一条记录...但是 sproc 继续处理相同的记录

PROCEDURE ScriptSendTrainingSurveyReminders 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    DECLARE @AttendeeID int
    DECLARE @TrainingAttendeeID int
    DECLARE @SurveyID int
    DECLARE @Message nvarchar(MAX)
    DECLARE @Subject nvarchar(255)
        CREATE TABLE #tSa
        (
            AttendeeID int,
            SurveyID int,
            TrainingAttendeeID int
        )
    SELECT
        TOP 1
        @AttendeeID=ta.AttendeeID,
        @SurveyID=ts.SurveyID,
        @TrainingAttendeeID = ta.TrainingAttendeeID
    FROM            
        TrainingAttendee ta
    INNER JOIN
        [User] u
    ON
        u.UserID= ta.AttendeeID
    INNER JOIN
        [Training] t
    ON
        t.TrainingID = ta.TrainingID
    INNER JOIN
        [TrainingSet] ts
    ON
        ts.TrainingSetID = t.TrainingSetID
    WHERE
        ta.AttendedTraining = 1
        AND ta.ConfirmAttendedEmailOn IS NOT NULL
        --only get people who didn't fill out the survey
        AND NOT EXISTS (SELECT * FROM SurveyTaken st WHERE st.AddedByUserID = ta.AttendeeID AND st.SurveyID = ts.SurveyID)
    ORDER BY
        ta.TrainingAttendeeID,
        ta.AttendeeID,
        ts.SurveyID
    --insert into our temp table
    INSERT INTO #tSa(AttendeeID, SurveyID, TrainingAttendeeID) 
    VALUES(@AttendeeID, @SurveyID, @TrainingAttendeeID)
    WHILE @TrainingAttendeeID IS NOT NULL AND @AttendeeID IS NOT NULL AND @SurveyID IS NOT NULL
        BEGIN
            DECLARE @TrainingID int
            DECLARE @Title nvarchar(50)
            DECLARE @StartDateTime nvarchar(50)
            DECLARE @EndDateTime nvarchar(50)
            DECLARE @FullName nvarchar(255)
            DECLARE @EmailAddress nvarchar(255)
            DECLARE @Description nvarchar(MAX)
            --get the one record we are on...
            SELECT 
                @TrainingID = t.TrainingID,
                @Title = t.Title,
                @StartDateTime = CAST(CONVERT(DATE, t.StartDate) AS VARCHAR(50)) + ' ' + CAST(t.StartTimeHours AS VARCHAR(50)) + ':' + CAST(CASE WHEN LEN(t.StartTimeMinutes)=1 THEN  CAST(t.StartTimeMinutes AS VARCHAR(50)) + '0' ELSE CAST(t.StartTimeMinutes AS VARCHAR(50)) END AS VARCHAR(50)) + ' ' + CAST(t.StartTimeAMorPM AS VARCHAR(50)),
                @EndDateTime = CAST(CONVERT(DATE, t.EndDate) AS VARCHAR(50)) + ' ' + CAST(t.EndTimeHours AS VARCHAR(50)) + ':' + CAST(CASE WHEN LEN(t.EndTimeMinutes)=1 THEN  CAST(t.EndTimeMinutes AS VARCHAR(50)) + '0' ELSE CAST(t.EndTimeMinutes AS VARCHAR(50)) END AS VARCHAR(50)) + ' ' + CAST(t.EndTimeAMorPM AS VARCHAR(50)),
                @Description = t.DescriptionHTML,
                @FullName = u.FullName,
                @EmailAddress = u.EmailAddress
            FROM 
                Training t 
            INNER JOIN
                TrainingAttendee ta
            ON
                t.TrainingID = ta.TrainingID
            INNER JOIN
                [User] u
            ON
                u.UserID = ta.AttendeeID
            WHERE
                ta.TrainingAttendeeID = @TrainingAttendeeID
            IF @EmailAddress IS NOT NULL
                BEGIN 
                      --Email goes out here....
                END
            --grab next one
                SELECT
                    TOP 1
                    @AttendeeID = ta.AttendeeID,
                    @SurveyID=ts.SurveyID,
                    @TrainingAttendeeID=ta.TrainingAttendeeID
                FROM            
                    TrainingAttendee ta
                INNER JOIN
                    [User] u
                ON
                    u.UserID= ta.AttendeeID
                INNER JOIN
                    [Training] t
                ON
                    t.TrainingID = ta.TrainingID
                INNER JOIN
                    [TrainingSet] ts
                ON
                    ts.TrainingSetID = t.TrainingSetID
                WHERE
                    (
                        --same where as original
                        (ta.AttendedTraining = 1)
                        AND (ta.ConfirmAttendedEmailOn IS NOT NULL)
                        AND (NOT EXISTS (SELECT * FROM SurveyTaken st WHERE st.AddedByUserID = ta.AttendeeID AND st.SurveyID = ts.SurveyID))
                        --adding the piece such that we compare against the temp table...
                        AND (NOT EXISTS (SELECT * FROM #tSa tempS WHERE tempS.AttendeeID = ta.AttendeeID AND tempS.SurveyID = ts.SurveyID AND tempS.TrainingAttendeeID = ta.TrainingAttendeeID))
                    )
                ORDER BY
                    ta.TrainingAttendeeID,
                    ta.AttendeeID,
                    ts.SurveyID
                PRINT CAST('TrainingAttendeeID: ' + CAST(@TrainingAttendeeID as nvarchar(500)) + ' AttendeeID:' + CAST(@AttendeeID as nvarchar(500)) + ' SurveyID: ' + CAST(@SurveyID as nvarchar(500)) AS nvarchar(4000))
                --insert into our temp table    
                INSERT INTO #tSa(AttendeeID, SurveyID, TrainingAttendeeID) 
                VALUES(@AttendeeID, @SurveyID, @TrainingAttendeeID)
        END
END
GO

Answer 1

如果 select 不返回任何记录，变量将不会更改。我敢打赌，它会持续@AttendeeID轮又一轮。

测试它的另一种方法 - 向临时表添加唯一约束。我假设一旦没有更多记录可供选择，循环就会失败。

修复它的一种方法 - 在每次迭代开始时（在正文顶部）将 NULL 分配给所有变量while。但是如果可能的话，我建议将此代码重写为光标（不确定几个选择语句的逻辑是什么）。

请注意，代码块中的变量声明没有"块范围"意义，因为它不是perl或python。