使用SQL Server 2016的嵌套While循环的While循环问题

Question

我正在使用SQL Server 2016，并且在使用嵌套的WHILE循环时迷失在杂草中。在内部WHILE循环中，我填充了一个包含PolicyNo和ClaimNo的临时表(#tempClaimRecordsForInsert)。总共有773条记录。临时表可以正确填充而不会出现问题。

问题出在外部WHILE循环中，我试图填充另一个临时表，试图从内部循环中创建的临时表(有16条UserSysID记录)向每个UserSysID添加773条声明。我在包含所有记录的临时表(#tempRecordsForInsert)中没有得到任何记录。任何帮助/指导都将不胜感激。谢谢。

下面是我的SQL代码：

DECLARE @counterUserID int = 1;
DECLARE @counterClaims int = 1;
CREATE TABLE #tempClaimRecordsForInsert (
    PolicyNo varchar(10)
    , ClaimNo varchar(6)
);
CREATE TABLE #tempRecordsForInsert (
    ReinsuranceAuditSysID int
    , PolicyNo varchar(10)
    , ClaimNo varchar(6)
)

WHILE @counterUserID <= (SELECT COUNT(*) FROM #tempAuditorUserIdList)
    BEGIN
        DECLARE @UserSysID int = (SELECT l.UserSysID FROM #tempAuditorUserIdList l WHERE l.RN = @counterUserID);

        WHILE @counterClaims <= (SELECT COUNT(*) FROM #ClaimNo)
            BEGIN
                DECLARE @RN1 int = (SELECT c.RN FROM #ClaimNo c WHERE c.RN = @counterClaims);

                INSERT INTO #tempClaimRecordsForInsert
                SELECT c.PolicyNo, c.ClaimNo --, x.RN
                FROM dbo.CMS_Claims c
                INNER JOIN #ClaimNo x
                ON c.ClaimNo = x.ClaimNo
                WHERE x.RN = @RN1;

                SET @counterClaims = (@counterClaims + 1)
            END

    INSERT INTO #tempRecordsForInsert
    SELECT @UserSysID as ReinsuranceAuditSysID, i.ClaimNo, i.PolicyNo
    FROM #tempClaimRecordsForInsert i
    WHERE @UserSysID = @counterUserID

    SET @counterUserID = (@counterUserID + 1)
END

SELECT * FROM #tempClaimRecordsForInsert;
DROP TABLE #tempClaimRecordsForInsert;
SELECT * FROM #tempRecordsForInsert;
DROP TABLE #tempRecordsForInsert;

Answer 1

看不到表数据是很难知道的，但是如果在#tempClaimRecordsForInsert中有UserSysId，那么外部插入的where子句应该是：

 UserSysID = (SELECT l.UserSysID FROM #tempAuditorUserIdList l WHERE l.RN = @counterUserID)

Answer 2

@Jayvee，我终于能够解决我的问题了，但再次感谢您抽出时间来看我的问题。以下是修复该问题的代码。我不需要把@UserSysID放在#tempClaimRecordsForInsert中，这让我很感激。我使用SQL Server 2016中的SSMS调试器逐句通过我的代码找到了答案。下面是其他人遇到同样问题时的代码：

CREATE TABLE #tempClaimRecordsForInsert (
    PolicyNo varchar(10)
    , ClaimNo varchar(6)
);
CREATE TABLE #tempRecordsForInsert (
    ReinsuranceAuditSysID int
    , PolicyNo varchar(10)
    , ClaimNo varchar(6)
);

DECLARE @counterUserID int = 1;
DECLARE @counterClaims int = 1;

WHILE @counterUserID <= (SELECT COUNT(*) FROM #tempAuditorUserIdList)
    BEGIN
        DECLARE @UserSysID int = (SELECT l.UserSysID FROM #tempAuditorUserIdList l WHERE l.RN = @counterUserID);
    
            WHILE @counterClaims <= (SELECT COUNT(*) FROM #ClaimNo)
                BEGIN
                    DECLARE @RN1 int = (SELECT c.RN FROM #ClaimNo c WHERE c.RN = @counterClaims);

                    INSERT INTO #tempClaimRecordsForInsert
                    SELECT c.PolicyNo, c.ClaimNo 
                    FROM dbo.CMS_Claims c
                    INNER JOIN #ClaimNo x
                    ON c.ClaimNo = x.ClaimNo
                    WHERE x.RN = @RN1;

                    SET @counterClaims = (@counterClaims + 1)
                END
                
        INSERT INTO #tempRecordsForInsert
        SELECT @UserSysID as ReinsuranceAuditSysID, i.PolicyNo, i.ClaimNo
        FROM #tempClaimRecordsForInsert i
        WHERE @UserSysID = (SELECT l.UserSysID FROM #tempAuditorUserIdList l WHERE l.RN = @counterUserID)

        SET @counterUserID = (@counterUserID + 1);
    END

SELECT * FROM #tempClaimRecordsForInsert ORDER BY ClaimNo asc;
SELECT * FROM #tempRecordsForInsert ORDER BY ReinsuranceAuditSysID asc, ClaimNo asc;
DROP TABLE #tempClaimRecordsForInsert;
DROP TABLE #tempRecordsForInsert;