CODEWITHSUNDEEP
mysql
Cilenco
Cilenco

Reputation: 7117

Create procedure in SQL fails

At the moment I'm trying to create a procedure in my databse with this query:

CREATE PROCEDURE InsertTitle(title VARCHAR(50), interpret VARCHAR(50), album VARCHAR(50))
BEGIN
    INSERT IGNORE INTO Interpret (Name) VALUES (interpret);
    SET @idInterpret := (SELECT id FROM Interpret WHERE Name = interpret);

    INSERT IGNORE INTO Album (Name, Interpret) VALUES (@album, @idInterpret);
    SET @idAlbum := (SELECT id FROM Interpret WHERE Name = album AND Interpret = @idInterpret);

    INSERT INTO Lied (Name, Album, Interpret) VALUES (title, @idAlbum, @idInterpret,);
END;

But now I get the following error which doesn't tell my anything:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3

Do you have an idea where my mistake is? I use a MySQL server.

Upvotes: 0

Views: 59

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

First, your problem might just be a delimited. Second, you are approaching the calculation of the inserted id incorrectly. You should use LAST_INSERT_ID():

DELIMITER $$

CREATE PROCEDURE InsertTitle(title VARCHAR(50), interpret VARCHAR(50), album VARCHAR(50))
BEGIN
    INSERT IGNORE INTO Interpret (Name)
        VALUES (interpret);
    SELECT @idInterpret :-= LAST_INSERT_ID()

    INSERT IGNORE INTO Album (Name, Interpret)
        VALUES (@album, @idInterpret);
    SELECT @idAlbum := LAST_INSERT_ID()

    INSERT INTO Lied (Name, Album, Interpret)
        VALUES (title, @idAlbum, @idInterpret);
END;
$$
DELIMITER ;

Also, set uses =, not :=. The latter is only needed in SELECT statements. And, you had an extra comma in the last VALUES() statement.

Upvotes: 2

Roman Marusyk
Roman Marusyk

Reputation: 24579

Try to use this solution:

delimiter //
CREATE PROCEDURE InsertTitle(title VARCHAR(50), interpret VARCHAR(50), album VARCHAR(50))
BEGIN
    INSERT IGNORE INTO Interpret (Name) VALUES (interpret);
    SET @idInterpret := (SELECT id FROM Interpret WHERE Name = interpret);

    INSERT IGNORE INTO Album (Name, Interpret) VALUES (@album, @idInterpret);
    SET @idAlbum := (SELECT id FROM Interpret WHERE Name = album AND Interpret = @idInterpret);

    INSERT INTO Lied (Name, Album, Interpret) VALUES (title, @idAlbum, @idInterpret,);
END//
delimiter ;

For more details please see: MySQL CREATE PROCEDURE

Upvotes: 0

Related Questions