JH Code Segment 1

<cfquery name="getSongs" datasource="MyDSN">
 select Bands.BandID, Bands.BandName, Songs.SongID, Songs.SongName
 from songs join albums_songs on (songs.songID = albums_songs.songID)
  join albums on (albums.albumID = albums_songs.AlbumID )
  join bands_albums on (bands_albums.albumID = Albums.albumID)
  join bands on (bands.bandID = bands_albums.bandID)
 where bandID = #variables.bandID#
</cfquery>


JH Code Segment 2

<cfquery name="getSongs" datasource="MyDSN">
 select BandID, BandName, SongID, SongName
 from SongsView
 where SongsView.bandID = #variables.bandID#
</cfquery>

JH Code Segment 3

CREATE PROCEDURE GetBandInfo

@BandID int

AS


select * from bands
where bands.bandID = @BandID

select albums.*
from albums, bands_albums
where bands_albums.albumID = albums.albumID and bands_albums.bandID = @BandID

select albums.albumID, songs.*
from albums, bands_albums, albums_songs, songs
where bands_albums.albumID = albums.albumID and
 bands_albums.bandID = @BandID and
 albums_songs.albumID = albums.albumID and
 songs.songID = albums_songs.songID
GO

?? JH Code Segment 4

<cfquery name="getSongs" datasource="MyDSN">
exec  GetBandInfo #variables.bandID#
</cfquery>

?? JH Code Segment 5

<cfstoredproc procedure="GetBandInfo" datasource="MyDSN">
 <cfprocparam value="#variables.bandID#" cfsqltype="cf_sql_integer">
 <cfprocresult name="getBand" resultSet="1">
 <cfprocresult name="getAlbum" resultSet="2">
 <cfprocresult name="getSong" resultSet="3">
</cfstoredproc>