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.