spRoleInUseByChapter

        CREATE procedure dbo.spRoleInUseByChapter
        (
            @ChaptersFoundUsingRole bit output,
            @ErrorMessage nvarchar(256) output,
            @ClientRoleID int   
        ) 

        as

        begin 
        declare @ChapterSiteName nvarchar(40)

        set @ChaptersFoundUsingRole = 0

        set @ErrorMessage = 'Unable to delete this role because it is used by the following Chapter Manager Part(s): '

        declare ChaptersUsingRole cursor local for
        select distinct SiteContent.[Title] from SiteContent inner join ChapterSite on SiteContent.[ID] = ChapterSite.SiteContentID
        inner join Chapters on ChapterSite.[ID] = Chapters.[ChapterSiteID] inner join ChapterRoles
        on Chapters.[ID] = ChapterRoles.[ChapterID]
        where ChapterRoles.[ClientRolesID] = @ClientRoleID

        open ChaptersUsingRole

        fetch next from ChaptersUsingRole
        into @ChapterSiteName

        while @@FETCH_STATUS = 0
        begin
        --STUFF ( character_expression , start , length , character_expression ) 
            select @ErrorMessage = @ErrorMessage +  @ChapterSiteName +  ', '
            set @ChaptersFoundUsingRole = 1

           fetch next from ChaptersUsingRole
           into @ChapterSiteName
        end

        close ChaptersUsingRole
        deallocate ChaptersUsingRole

        select @ErrorMessage = LEFT(@ErrorMessage, LEN(@ErrorMessage) - 1)

        end