MySQL create procedure declare variable issues

Question

I've asked for help on this a few days ago, I didn't receive an answer. Or any comments. I apologize if I am getting annoying, I'm just trying to use outlets available to me to complete my classwork.

Use a variable to write a procedure called "AddNewAlbum." This procedure takes at least two parameters - NameOfArtist and AlbumName. The procedure will:

1.Lookup ArtistID from the Artist table, where artist name is NameOfArtist

2.Insert a new row into Albums, using the ArtistID found in step #1 and the AlbumName parameter

For now it is safe to assume the artist exists before you run this procedure (meaning if you CALL AddNewAlbum ('Bob Dylan', 'Street Legal'); then "Bob Dylan" already exists in the Artist table.

That is what I need to do ^

CREATE PROCEDURE AddNewAlbum (
NameOfArtist varchar(50),
AlbumName varchar(50)
)
BEGIN

DECLARE artist_id varchar(50);

SELECT ArtistID into artist_id
FROM Artists
WHERE ArtistName = NameOfArtist;

INSERT INTO Albums (Title)
VALUES (AlbumName);
END;
//

call AddNewAlbum (
"Bob Dylan",
"Street Legal"
);
//

ERROR 1364 (HY000): Field 'ArtistID' doesn't have a default value

That is the error returning. I've attempted to write this a few other ways, and the same error returns.

Answer 1

Try this. added a default value and updates your procedure a bit to match the input

CREATE PROCEDURE AddNewAlbum (
NameOfArtist varchar(50),
AlbumName varchar(50)
)
BEGIN

DECLARE artist_id INT DEFAULT 0;

SELECT ArtistID into artist_id
FROM Artists
WHERE ArtistName = NameOfArtist;

INSERT INTO Albums (ArtistID, Title)
VALUES (artist_id, AlbumName);
END;
//

call AddNewAlbum (
"Bob Dylan",
"Street Legal"
);
//