Syntax error near END keyword?

Question

I am defining this SQL Server SP, but I am getting the following error message, which is not very detailed:

Incorrect syntax near the keyword 'end'. 32 8

I am closing all BEGIN with an END, therefore I can't get it why is the engine complaining.

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

What is wrong with the END?

you have a begin .. end without any code in it. Sql server does not likes that, remove it and the error will be gone — GuidoG
– GuidoG, Commented Oct 10, 2016 at 10:41
Remove the else part of the mention after @lastReading IS NOT NULL — Bhavika Zimbar
– Bhavika Zimbar, Commented Oct 10, 2016 at 10:42

Pரதீப் · Accepted Answer · 2016-10-10 10:48:04Z

From MSDN

BEGIN  
    { sql_statement | statement_block }   
END

{ sql_statement | statement_block }

Is any valid Transact-SQL statement or statement grouping as defined by using a statement block.

You need to have a valid Transact-SQL statement between Begin and END, so cannot have this

 ELSE
     BEGIN
     -- Do nothing
     END

If your ELSE part is not going to do anything then remove it

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

Joe Taras · Accepted Answer · 2016-10-10 10:41:37Z

1

If you declare ELSE statement it can't be empty. So if you have only BEGIN END it goes in error.

If you don't want to use ELSE, remove it.

answered Oct 10, 2016 at 10:41

Joe Taras

15.4k7 gold badges45 silver badges59 bronze badges

Comments

user2311103 · Accepted Answer · 2016-10-10 10:42:43Z

1

The problem is the else. You must put code between BEGIN and END. The commented Do nothing doesn't nothing. You could add a SELECT 0 if you want add something else later, else you should delete it.

answered Oct 10, 2016 at 10:42

user2311103

193 bronze badges

Comments

TheGameiswar · Accepted Answer · 2016-10-10 11:07:40Z

0

Begin and END statement need minimum one line of code

 BEGIN
      -- Do nothing
        PRINT 1
  END

edited Oct 10, 2016 at 11:07

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

answered Oct 10, 2016 at 10:53

KCP

113 bronze badges

Collectives™ on Stack Overflow

Syntax error near END keyword?

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related