1

I am rewriting my database system from MySql to MSSQL. In my current piece of code i get the error:

SQL80001: Incorrect syntax near ';'. Expecting CONVERSATION.

CREATE PROCEDURE [dbo].[addAnime]

@$animeID INT,
@$animeName VARCHAR(100),
@$animeEpisodes INT,
@$animeSeasons INT,
@$animeGenre1 VARCHAR(100),
@$animeGenre2 VARCHAR(100),
@$animeGenre3 VARCHAR(100),
@$animeArtist VARCHAR(100),
@$animeStudio VARCHAR(100),
@$animeReleaseDate VARCHAR(100),
@$animeDescription VARCHAR(8000),
@$animeImageLocation VARCHAR(1000)

AS
BEGIN

IF NOT EXISTS (SELECT * FROM animedatabase.anime WHERE animeName LIKE @$animeName)

    INSERT INTO animedatabase.anime(animeID, animeName, episodes, seasons, genre1, genre2, genre3, artist, studio, releaseDate, animeDescription, imageLocation) 
    VALUES(@$animeID, @$animeName, @$animeEpisodes, @$animeSeasons, @$animeGenre1, @$animeGenre2, @$animeGenre3, @$animeArtist, @$animeStudio, @$animeReleaseDate, @$animeDescription, @$animeImageLocation);

    ELSE IF ((SELECT COUNT(*) FROM animedatabase.anime WHERE animeName LIKE @$animeName) = 1)

        UPDATE
            animedatabase.anime
        SET 
            animeID = @$animeID,
            animeName = @$animeName,
            episodes = @$animeEpisodes,
            seasons = @$animeSeasons,
            genre1 = @$animeGenre1,
            genre2 = @$animeGenre2,
            genre3 = @$animeGenre3,
            artist = @$animeArtist,
            studio = @$animeStudio,
            releaseDate = @$animeReleaseDate,
            animeDescription = @$animeDescription,
            imageLocation = @$animeImageLocation
        WHERE animedatabase.anime.animename LIKE @$animeName;

    END

END

END;

I've tried quite a lot of changes in structure and naming, but none solved the problem. I can't seem to find what I'm doing wrong. Any help would be much appreciated.

Thanks in advance.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
DemonWyvern
  • 33
  • 5
  • 3
    Whenever you have an `IF` struct (and you have 2), always add `BEGIN` / `END`. Try adding a `BEGIN` before `UPDATE`. But also, I've found [a much more efficient way to do this](http://stackoverflow.com/a/21209295/61305): `UPDATE ... WHERE animeName LIKE... / IF @@ROWCOUNT = 0 / INSERT`. This skips one table access and potentially a full table scan depending on the value of the parameter and whether an index exists on that column. – Aaron Bertrand Feb 17 '17 at 18:30
  • This worked like a charm!! I cannot thank you enough for this! :) – DemonWyvern Feb 17 '17 at 21:08

0 Answers0