spPopulateEmailRecipientsForRole

CREATE PROCEDURE [dbo].[spPopulateEmailRecipientsForRole]
(
@EmailID int,
@RoleID int
)
AS
BEGIN

    declare @ROLEIDS table (ID int)
    insert into @ROLEIDS values(@RoleID)

    insert into @ROLEIDS
    select * from dbo.UFN_CLIENTROLE_GETCHILDIDS(@RoleID)    

    INSERT INTO
    dbo.Email_Recipient
    (
        [EmailID],
        [UserID], 
        [DisplayName], 
        [EmailAddress]
    )     

    (SELECT 
    @EmailID as 'EmailID',
    cu.ID as 'UserID',
    ISNULL(cu.Firstname,'') + ' ' + ISNULL(cu.MiddleInitial,'') + ' ' + ISNULL(cu.LastName,'') as 'DisplayName',
    cu.Email as 'EmailAddress'
    FROM dbo.UserRoles ur
    INNER JOIN @ROLEIDS r on r.ID = ur.ClientRolesID
    INNER JOIN ClientUsers cu on cu.ID = ur.ClientUsersID
    WHERE cu.Deleted = 0)

END