Incorrect syntax near the keyword 'ELSE'

sql-server tsql
16,401

Solution 1

I have commented out some of the suspicious ENDs have a look now, With such a complex query Indentation really make things much easier.

IF (Not Exists(SELECT * FROM Log_DB.dbo._LogJobSYS WHERE KillerJobID=@JKillerID AND DeadJobID=@CharID)) 
    BEGIN
        INSERT Log_DB.dbo._LogJobSYS(KillerJobID,DeadJobID,DeathTime)
        VALUES (@JKillerID, @CharID, GETDATE())
        BEGIN

                IF (@JKillerJobType = 1) /*TRADER*/ 
                    BEGIN
                        IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobTradersys Where CharID=@JKillerID))
                          BEGIN
                             INSERT SRO_VT_SHARD_INIT.dbo._CharJobTradersys(CharID,TraderPoints,Kills,Deaths,LastKill,Date)
                             VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                           END
                        ELSE 
                          Begin
                            UPDATE SRO_VT_SHARD_INIT.dbo._CharJobTradersys SET TraderPoints = TraderPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                          END
                    END
                    --END  --<-- This End

                    ELSE IF (@JKillerJobType = 2) /*THIEF*/ 
                      BEGIN
                             IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobThiefsys Where CharID=@JKillerID))
                                BEGIN
                                    INSERT SRO_VT_SHARD_INIT.dbo._CharJobThiefsys(CharID,ThiefPoints,Kills,Deaths,LastKill,Date)
                                    VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                                END
                        ELSE 
                            Begin
                                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobThiefsys SET ThiefPoints = ThiefPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                            END

                      END

                    ELSE IF (@JKillerJobType = 3) /*HUNTER*/ 
                      BEGIN
                            IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobHuntersys Where CharID=@JKillerID))
                                BEGIN
                                    INSERT SRO_VT_SHARD_INIT.dbo._CharJobHuntersys(CharID,HunterPoints,Kills,Deaths,LastKill,Date)
                                    VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                                END
                            ELSE 
                                Begin
                                    UPDATE SRO_VT_SHARD_INIT.dbo._CharJobHuntersys SET HunterPoints = HunterPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                            END

                      END 
                    BEGIN IF (@JDeadJobType = 1) /*TRADER*/ 
                        BEGIN
                                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobTradersys WHERE CharID=@CharID))
                                    BEGIN
                                        INSERT SRO_VT_SHARD_INIT.dbo._CharJobTradersys(CharID,TraderPoints,Kills,Deaths,LastKill,Date)
                                        VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                                    END
                                ELSE 
                                    BEGIN
                                        UPDATE SRO_VT_SHARD_INIT.dbo._CharJobTradersys SET TraderPoints = TraderPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                                    END
                        END
                    --END   --<--  this End  
                    ELSE  IF (@JDeadJobType = 2) /*THIEF*/ 
                        BEGIN
                                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobThiefsys WHERE CharID=@CharID))
                                    BEGIN
                                        INSERT SRO_VT_SHARD_INIT.dbo._CharJobThiefsys(CharID,ThiefPoints,Kills,Deaths,LastKill,Date)
                                        VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                                    END
                                ELSE 
                                    BEGIN
                                        UPDATE SRO_VT_SHARD_INIT.dbo._CharJobThiefsys SET ThiefPoints = ThiefPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                                END

                        END
                    ELSE IF (@JDeadJobType = 3) /*HUNTER*/ 
                        BEGIN
                                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobHuntersys WHERE CharID=@CharID))
                                    BEGIN
                                        INSERT SRO_VT_SHARD_INIT.dbo._CharJobHuntersys(CharID,HunterPoints,Kills,Deaths,LastKill,Date)
                                        VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                                    END
                                ELSE 
                                    BEGIN
                                        UPDATE SRO_VT_SHARD_INIT.dbo._CharJobHuntersys SET HunterPoints = HunterPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                             END

                      END
               END  
          END
   END

Solution 2

It looks like you may be missing a few 'END's. Unless you mean to nest deeper. For the TRADER type, you close all the way out, but for THIEF on down you do not.

BEGIN
        BEGIN
            IF (@JKillerJobType = 1) /*TRADER*/ 
            BEGIN
                IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobTradersys Where CharID=@JKillerID))
                BEGIN
                    INSERT SRO_VT_SHARD_INIT.dbo._CharJobTradersys(CharID,TraderPoints,Kills,Deaths,LastKill,Date)
                    VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE 
                Begin
                    UPDATE SRO_VT_SHARD_INIT.dbo._CharJobTradersys SET TraderPoints = TraderPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END
            END
        END
        ELSE 
            IF (@JKillerJobType = 2) /*THIEF*/ 
            BEGIN
                IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobThiefsys Where CharID=@JKillerID))
                BEGIN
                    INSERT SRO_VT_SHARD_INIT.dbo._CharJobThiefsys(CharID,ThiefPoints,Kills,Deaths,LastKill,Date)
                    VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE 
                Begin
                    UPDATE SRO_VT_SHARD_INIT.dbo._CharJobThiefsys SET ThiefPoints = ThiefPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END
            END
            ELSE 
                IF (@JKillerJobType = 3) /*HUNTER*/ 
                BEGIN
                    IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobHuntersys Where CharID=@JKillerID))
                    BEGIN
                    INSERT SRO_VT_SHARD_INIT.dbo._CharJobHuntersys(CharID,HunterPoints,Kills,Deaths,LastKill,Date)
                    VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE 
                Begin
                    UPDATE SRO_VT_SHARD_INIT.dbo._CharJobHuntersys SET HunterPoints = HunterPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END
Share:
16,401

Related videos on Youtube

Muhab
Author by

Muhab

Updated on June 26, 2022

Comments

  • Muhab
    Muhab about 2 years

    I got a problem with else function.

    Here is my code 

    IF (Not Exists(SELECT * FROM Log_DB.dbo._LogJobSYS WHERE KillerJobID=@JKillerID AND DeadJobID=@CharID)) 
    BEGIN
        INSERT Log_DB.dbo._LogJobSYS(KillerJobID,DeadJobID,DeathTime)
        VALUES (@JKillerID, @CharID, GETDATE())
        BEGIN
            BEGIN
                IF (@JKillerJobType = 1) /*TRADER*/ 
                BEGIN
                IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobTradersys Where CharID=@JKillerID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobTradersys(CharID,TraderPoints,Kills,Deaths,LastKill,Date)
                VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE Begin
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobTradersys SET TraderPoints = TraderPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END
                END
            END
            ELSE 
                IF (@JKillerJobType = 2) /*THIEF*/ 
                BEGIN
                IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobThiefsys Where CharID=@JKillerID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobThiefsys(CharID,ThiefPoints,Kills,Deaths,LastKill,Date)
                VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE Begin
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobThiefsys SET ThiefPoints = ThiefPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END

            END
            ELSE 
                IF (@JKillerJobType = 3) /*HUNTER*/ 
                BEGIN
                IF (Not Exists(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobHuntersys Where CharID=@JKillerID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobHuntersys(CharID,HunterPoints,Kills,Deaths,LastKill,Date)
                VALUES (@JKillerID, 1, 1, 0, @JDeadNick, GETDATE())
                END
                ELSE Begin
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobHuntersys SET HunterPoints = HunterPoints +1, Kills = Kills +1, LastKill = @JDeadNick, Date = GETDATE() WHERE CharID = @JKillerID
                END

            END 
            BEGIN
                IF (@JDeadJobType = 1) /*TRADER*/ 
                BEGIN
                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobTradersys WHERE CharID=@CharID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobTradersys(CharID,TraderPoints,Kills,Deaths,LastKill,Date)
                VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                END
                ELSE BEGIN
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobTradersys SET TraderPoints = TraderPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                END
                END
            END 
            ELSE  
                IF (@JDeadJobType = 2) /*THIEF*/ 
                BEGIN
                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobThiefsys WHERE CharID=@CharID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobThiefsys(CharID,ThiefPoints,Kills,Deaths,LastKill,Date)
                VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                END
                ELSE BEGIN
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobThiefsys SET ThiefPoints = ThiefPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                END

            END
            ELSE 
                IF (@JDeadJobType = 3) /*HUNTER*/ 
                BEGIN
                IF (NOT EXISTS(SELECT * FROM SRO_VT_SHARD_INIT.dbo._CharJobHuntersys WHERE CharID=@CharID))
                BEGIN
                INSERT SRO_VT_SHARD_INIT.dbo._CharJobHuntersys(CharID,HunterPoints,Kills,Deaths,LastKill,Date)
                VALUES (@CharID, 0, 0, 1, 'NONE', GETDATE())
                END
                ELSE BEGIN
                UPDATE SRO_VT_SHARD_INIT.dbo._CharJobHuntersys SET HunterPoints = HunterPoints -0.5, Deaths = Deaths +1, Date = GETDATE() WHERE CharID = @CharID
                END

            END
        END
    END

    I get these errors:

    Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 155
    Incorrect syntax near the keyword 'ELSE'.

    Msg 156, Level 15, State 1, Procedure _AddLogChar, Line 194
    Incorrect syntax near the keyword 'ELSE'.

    Side note : this is for the first Else command but the second one doesn't give error (the ones which are before IF (@JKillerJobType = 2) AND IF (@JDeadJobType = 2) ! thanks !

  • Muhab
    Muhab about 10 years
    _AddLogChar is the name of the stored procedures !
  • Anthony Horne
    Anthony Horne about 10 years
    Sorry. Not entirely clear. My next guess was @mgoodric's. With all of that, mostly likely missing or too many END's or BEGINS. Is there any reason why you cannot simply it using a few case statements. There is a lot of duplication around CharJobThiefsys and CharJobHuntSys. Case when CharJobThier is null and CharJobHunt is not null, etc.
  • Muhab
    Muhab about 10 years
    when i tried to remove the ends with close the way and but it at the last of SP it executed successfully but only the last command which of IF (@JDeadJobType = 1) /*TRADER*/ (look for it in main question) is working , others not
  • Muhab
    Muhab about 10 years
    thanks it works just seems i miss smth which is responsible to declare of @JKillerJobType so it's not working :s
  • Muhab
    Muhab about 10 years
    thanks alot just copied it and it's working also i know where was the errors as u said :D
  • M.Ali
    M.Ali about 10 years
    Wow really didnt expect it to :) well glad it worked all I did was properly indented it which makes easier to read complex code.

Recents

Why Is PNG file with Drop Shadow in Flutter Web App Grainy?
How to troubleshoot crashes detected by Google Play Store for Flutter app
Cupertino DateTime picker interfering with scroll behaviour
Why does awk -F work for most letters, but not for the letter "t"?
Flutter change focus color and icon color but not works
How to print and connect to printer using flutter desktop via usb?
Critical issues have been reported with the following SDK versions: com.google.android.gms:play-services-safetynet:17.0.0
Flutter Dart - get localized country name from country code
navigatorState is null when using pushNamed Navigation onGenerateRoutes of GetMaterialPage
Android Sdk manager not found- Flutter doctor error
Flutter Laravel Push Notification without using any third party like(firebase,onesignal..etc)
How to change the color of ElevatedButton when entering text in TextField

Related

TSQL Add two Numbers
SQl: Update Table from a text File
How to iterate through each row in sql server?
Does using fully qualified names affect performance?
Updating one column with other's value in same table
Deleting orphans from a table
How to UPDATE TOP 400?
SQL Server : reset variable value
Specified "COLLATE Latin1_General_CS_AS", query result still "case insensitive"
yearweek on SQL server