CREATE FUNCTION [dbo].[func_GetArtistIDsForSong] 
(
  @SongName NVARCHAR(200)
)
RETURNS TABLE
AS
   RETURN (
   SELECT Albums.artistID FROM Albums, Songs
   WHERE Songs.name LIKE @SongName + '%'
   AND Songs.albumID = Albums.id)
GO

CREATE FUNCTION [dbo].[func_AllMusiciansForSong]
(
 @SongID INT
 )

RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @rVal NVARCHAR(MAX) 
DECLARE @iVal NVARCHAR(MAX) 
SET @RVal = ''
SET @iVal = ''
DECLARE c CURSOR FOR
 SELECT Musician.name + ':'  + InstrumentPlayed.name + '  '
          FROM Musician, InstrumentPlayed
          WHERE InstrumentPlayed.SongID = @SongID
          AND InstrumentPlayed.MusicianID = Musician.id

OPEN c
 FETCH NEXT FROM c INTO @iVal
 WHILE (@@FETCH_STATUS <> -1)
  BEGIN
   SET @rVal = @rVal + @iVal
   FETCH NEXT FROM c INTO @iVal
  END
 CLOSE c
DEALLOCATE c

RETURN(@rVal)          
END

CREATE FUNCTION [dbo].[func_GetAlbumName]
(
 @AlbumID INT
)
RETURNS NVARCHAR(200)
AS
BEGIN
  RETURN(SELECT TOP 1 name FROM Albums WHERE id = @AlbumID)
END

CREATE FUNCTION [dbo].[func_GetAlbumID]
(
 @AlbumName NVARCHAR(200)
)
RETURNS INT
AS
BEGIN
DECLARE @AlbumCount INT
SET @AlbumCount = (SELECT Count(*) FROM Albums WHERE Name = @AlbumName)
IF @AlbumCount >= 1
BEGIN
  RETURN (SELECT TOP 1 id FROM Albums WHERE name = @AlbumName)
END
RETURN(0)
END