tsql - SQL 卡住，否则语法错误

Question

我遇到了一个 ELSE 语法错误，非常感谢第二组眼睛告诉我出了什么问题！我正在使用 SSMS v17.3。我收到的消息是：

消息 156，第 15 级，状态 1，第 98 行

关键字“ELSE”附近的语法不正确。

第 98 行是 go 之前的最后一个 ELSE。在整个语句的结尾上方大约 6 行。提前谢谢你！

DECLARE @GroupID uniqueidentifier
DECLARE @fCompID int
DECLARE @fPropID int
DECLARE @fCompGUID uniqueidentifier

Set @GroupID = 'E63DC5E7-C8C8-4EA3-B1BF-75712DD83EF4'
SET @fCompID = '0'
SET @fPropID = '0'

WHILE @fCompID <= '999'
    BEGIN
    IF @fCompID = (SELECT fID FROM tSCCompany WHERE fID = @fCompID)
        SET @fCompGUID = (SELECT fCompanyID FROM tSCCompany WHERE fID = @fCompID)
        WHILE @fPropID <='999'
            BEGIN
            IF @fPropID = (SELECT fID FROM tSCProperty WHERE fID = @fPropID AND fCompanyID = @fCompGUID)
                BEGIN
                INSERT INTO zPropTest(fGroupID, fPropertyID)
                    Select @GroupID, (select fPropertyID from tSCProperty where fID = @fPropID AND fCompanyID = @fCompGUID)
                SET @fPropID = @fPropID + 1
                END
            ELSE
                BEGIN
                SET @fPropID = @fPropID + 1
                END
            END
    ELSE
        BEGIN
        SET @fCompID = @fCompID + 1
        SET @fPropID = '0'
        END
    END
GO

标签： tsqlif-statementwhile-loopsql-server-2012

Answer 1

我认为你错过了TOP 1

DECLARE @GroupID uniqueidentifier
DECLARE @fCompID int
DECLARE @fPropID int
DECLARE @fCompGUID uniqueidentifier

Set @GroupID = 'E63DC5E7-C8C8-4EA3-B1BF-75712DD83EF4'
SET @fCompID = '0'
SET @fPropID = '0'

WHILE @fCompID <= '999'
    BEGIN
        IF @fCompID = (SELECT fID FROM tSCCompany WHERE fID = @fCompID) -- Need top 1?
            SET @fCompGUID = (SELECT fCompanyID FROM tSCCompany WHERE fID = @fCompID) -- Need top 1?
            WHILE @fPropID <='999'
                BEGIN
                    IF @fPropID = (SELECT fID FROM tSCProperty WHERE fID = @fPropID AND fCompanyID = @fCompGUID) -- Need top 1?
                        BEGIN
                            INSERT INTO zPropTest(fGroupID, fPropertyID)
                                Select @GroupID, 
                                    (select top 1 fPropertyID from tSCProperty 
                                        where fID = @fPropID AND fCompanyID = @fCompGUID)
                            SET @fPropID = @fPropID + 1
                        END
                    ELSE
                        BEGIN
                            SET @fPropID = @fPropID + 1
                        END
                END
        ELSE
            BEGIN
                SET @fCompID = @fCompID + 1
                SET @fPropID = '0'
            END
    END
GO