USP_DATALIST_CLIENTROLE_TASKS

CREATE procedure dbo.USP_DATALIST_CLIENTROLE_TASKS(@CLIENTROLEID int)
as
    set nocount on;

  declare @TASKS table
  (
    TASKGUID uniqueidentifier,
    CLIENTSITESID int,
    ISSITETASK bit,
    APPLYTOCHILDREN bit
  )

  insert into @TASKS
    select SGT.TASKGUID,
       SGCRCS.CLIENTSITESID,
       SG.SITETASKSGROUP as ISSITETASK,
       case when sum(cast(SGCRCS.APPLYTOCHILDREN as int)) > 0 then 1 else 0 end as APPLYTOCHILDREN
  from CMSSECURITYGROUPCLIENTROLECLIENTSITE SGCRCS
  inner join CMSSECURITYGROUP SG  on SG.ID = SGCRCS.CMSSECURITYGROUPID
  inner join CMSSECURITYGROUPTASK SGT on SGT.CMSSECURITYGROUPID = SG.ID
  where SGCRCS.CLIENTROLESID = @CLIENTROLEID
  group by SGT.TASKGUID, SGCRCS.CLIENTSITESID, SG.SITETASKSGROUP     

  insert into @TASKS
  select tasks.TASKGUID, child.ID, tasks.ISSITETASK, tasks.APPLYTOCHILDREN
  from    (select * 
          from @TASKS
          where CLIENTSITESID is not null and APPLYTOCHILDREN = 1) tasks        
  cross apply dbo.UFN_CLIENTSITE_GETCHILDIDS(tasks.CLIENTSITESID) child

  select TASKGUID, CLIENTSITESID, ISSITETASK
  from @TASKS