| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Spring 2013 COP4709 Assignment 6 Solution

Page history last edited by Dr. Ron Eaglin 11 years, 4 months ago

Spring 2013 COP4709 Assignment 6 Solution

 

To complete this I simply broke it up into a number of stored procedures, that called the related stored procedures. By creating a wrapper around each cursor it is much easier to debug and follow the logic. It is also easy to test each stored procedure to make sure it is working correctly. I highly recommend breaking your stored procedures and functions into smaller units for this purpose.

 

The first stored procedure is sp_FullReport - not that it calls the stored procedure

 

CREATE PROCEDURE [dbo].[sp_FullReport]
AS
BEGIN

DECLARE artist_Cursor CURSOR 
 FOR SELECT id, name FROM Artists
   
DECLARE @id INT
DECLARE @name NVARCHAR(200)  

OPEN artist_Cursor
FETCH NEXT FROM artist_Cursor INTO @id, @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   PRINT 'Artist:' + @name
   EXEC sp_albumReport @id  
   PRINT ' '

   FETCH NEXT FROM artist_Cursor INTO @id, @name

END
CLOSE artist_Cursor
DEALLOCATE artist_Cursor
END

 

 

Note that this stored procedure calls sp_AlbumReport with the id of the album

 

CREATE PROCEDURE [dbo].[sp_AlbumReport] (
 @artistID INT
 )
AS 
BEGIN
DECLARE album_Cursor CURSOR 
 FOR SELECT id, name FROM Albums WHERE artistID = @artistID
   
DECLARE @id INT
DECLARE @name NVARCHAR(200)  
OPEN album_Cursor
FETCH NEXT FROM album_Cursor INTO @id, @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   PRINT ' Album:' + @name
 
   EXEC sp_SongReport @id
   FETCH NEXT FROM album_Cursor INTO @id, @name
END
CLOSE album_Cursor
DEALLOCATE album_Cursor
END 
 

 

And this stored procedure calls sp_SongReport with the Song id

 

CREATE PROCEDURE [dbo].[sp_SongReport] (
 @albumID INT
 )
AS 
BEGIN
DECLARE song_Cursor CURSOR 
 FOR SELECT TrackNumber, id, name 
 FROM Songs 
 WHERE albumID = @albumID
 ORDER BY TrackNumber ASC
DECLARE @track INT   
DECLARE @id INT
DECLARE @name NVARCHAR(200)  
OPEN song_Cursor
FETCH NEXT FROM song_Cursor INTO @track, @id, @name
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   PRINT '  ' + CAST(@track AS VARCHAR(3)) + '.  Song:' + @name
    
   EXEC sp_MusicianReport @id
   FETCH NEXT FROM song_Cursor INTO @track, @id, @name
END
CLOSE song_Cursor
DEALLOCATE song_Cursor
END 

 

And this stored procedure calls sp_MusicianReport with the Musician ID as an argument

 

CREATE PROCEDURE [dbo].[sp_MusicianReport] (
 @songID INT
 )
AS 
BEGIN
DECLARE m_Cursor CURSOR 
 FOR 
 SELECT InstrumentPlayed.name as IName, Musician.name as MName
 FROM InstrumentPlayed, Musician 
 WHERE songID = @songID
 AND InstrumentPlayed.MusicianID = Musician.id 
   
DECLARE @IName NVARCHAR(200)
DECLARE @MName NVARCHAR(200)  
OPEN m_Cursor
FETCH NEXT FROM m_Cursor INTO @IName, @MName
WHILE (@@FETCH_STATUS <> -1)
BEGIN
   PRINT '    Instrument: ' + @IName +' : ' + @MName
    
   FETCH NEXT FROM m_Cursor INTO @IName, @MName
END
CLOSE m_Cursor
DEALLOCATE m_Cursor
END 
 

 

 

 

 

Comments (0)

You don't have permission to comment on this page.