For this project, create a new stored procedure called AddAlbum . It will take a

Question

For this project, create a new stored procedure called AddAlbum. It will take at least two parameters: NameOfArtist and AlbumName.

Your procedure needs to check the Artists table to see if a NameOfArtist exists. If it does not exist, you'll need to add a new row. Here are the steps:

Use the COUNT aggregate on Artists to see how many rows exist for NameOfArtist. Store the count in a variable called artist_count.

If artist_count is zero, insert a new row into Artists. Select the LAST_INSERT_ID() into a variable.

If artist_count is one, lookup the ArtistID and store it in a variable.

Insert a new row into the Albums table.

Explanation / Answer

DECLARE   artist_count    INT     DEFAULT     0;

DECLARE lastInsertId    INT   DEFAULT 0;

DECLARE ArtistID, ArtistsName, Age, AlbumID , AlbumName;

Create           Procedure            c1

            ( @var1   int   output ) AS

            SELECT @var1 = count(*) from ArtistTable;

SELECT @@ROWCOUNT into @artist_count;

Create Procedure AddAlbum

            (NameOfArtist String, AlbumName String) AS

            c1;     -- calling the Counting Procedure   c1

;

IF @artist_count == 0

BEGIN

            INSERT INTO ArtistsTable

                        VALUES (@ArtistName, @Age, @AlbumID, @ArtistID);

            SELECT LAST_INSERT_ID INTO @lastInsertId;

END

IF @artist_count == 1

BEGIN

            SELECT ArtistID INTO @ArtistID FROM ArtistsTable

                        WHERE AlbumId = @AlbumId;

            INSERT INTO AlbumsTable VALUES

                        (@AlbumID, @AlbumName, @ArtistID);

END

GO

EXEC AddAlbum

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.