END关键字附近有语法错误

Question

我正在定义这个SQL Server SP，但我得到以下错误消息，这不是很详细:

Incorrect syntax near the keyword 'end'. 32 8

我用一个END关闭所有的BEGIN，因此我不能得到它，为什么引擎抱怨。

CREATE PROCEDURE dbo.addReading
    @deviceId int,
    @facilityId int,
    @reading real,
    @insertionTimestamp datetime2,
    @isMeter bit
AS BEGIN
    IF (@isMeter = 1)
        BEGIN
            DECLARE @lastReading real;
            DECLARE @newReading real;
            -- Get last reading
            SELECT @lastReading = lastReading FROM devices
            WHERE facilityId = @facilityId AND id = @deviceId;
            -- Update lastReading with the new one
            UPDATE devices SET lastReading = @reading
            WHERE facilityId = @facilityId AND id = @deviceId;
            IF (@lastReading IS NOT NULL)
                BEGIN
                    SET @newReading = @reading - @lastReading;
                    INSERT INTO readings (deviceId, facilityId, reading, insertionTimestamp)
                    VALUES (@deviceId, @facilityId, @newReading, @insertionTimestamp);
                END
            ELSE
                BEGIN
                    -- Do nothing
                END
        END   -- ---------------------------------- LINE 32 (ERROR HERE!)
    ELSE
        BEGIN
            INSERT INTO readings (deviceId, facilityId, reading, insertionTimestamp)
            VALUES (@deviceId, @facilityId, @reading, @insertionTimestamp);
        END
END
GO

END有什么问题?

Answer 1

From MSDN

BEGIN  
    { sql_statement | statement_block }   
END  

{sql_statement | statement_block}

是定义的任何有效的Transact-SQL语句或语句组

您需要在Begin和END之间有一个有效的Transact-SQL语句，所以不能有这个

 ELSE
     BEGIN
     -- Do nothing
     END

如果你的ELSE部分不打算做任何事情，然后删除它

IF (@isMeter = 1)
    BEGIN
        DECLARE @lastReading real;
        DECLARE @newReading real;
        -- Get last reading
        SELECT @lastReading = lastReading FROM devices
        WHERE facilityId = @facilityId AND id = @deviceId;
        -- Update lastReading with the new one
        UPDATE devices SET lastReading = @reading
        WHERE facilityId = @facilityId AND id = @deviceId;
        IF (@lastReading IS NOT NULL)
            BEGIN
                SET @newReading = @reading - @lastReading;
                INSERT INTO readings (deviceId, facilityId, reading, insertionTimestamp)
                VALUES (@deviceId, @facilityId, @newReading, @insertionTimestamp);
            END
    END 

Answer 2

如果你声明ELSE语句，它不能为空。所以如果你只有BEGIN END，它就会出错。

如果您不想使用ELSE，请删除它

Answer 3

问题在于else。必须在BEGIN和END之间放置代码。评论说什么都不做不是什么都不做。你可以添加一个SELECT 0，如果你想添加其他东西，否则你应该删除它。

Answer 4

Begin and END语句最少需要一行代码

 BEGIN
      -- Do nothing
        PRINT 1
  END