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 |