spGetDataSourcesForTemplates

CREATE  PROCEDURE [dbo].[spGetDataSourcesForTemplates]
(
    @ExcludeBackOfficeSystem nvarchar(max),
    @ClientUsersID int = null
)            
as
    -- Null out the client users ID if they are supervisor
    if (@ClientUsersID is not null)
        if ((select CU.IsSupervisor | CU.InternalUser from dbo.ClientUsers CU where CU.ID = @CLIENTUSERSID) = 1)
            set @ClientUsersID = null;

    declare @ClientSites TABLE (ClientSitesID int);
    if (@ClientUsersID is not null)
        insert into @ClientSites
        select CLIENTSITESID from [dbo].[UFN_CLIENTUSERS_TASKS](@ClientUsersID,'034D2C4C-C6C7-4E55-8D4B-77C216EE3E25')
        union
        select CLIENTSITESID from [dbo].[UFN_CLIENTUSERS_TASKS](@ClientUsersID,'E81C3046-A612-437A-A03C-1F75FBD76C6E')

    if exists(select 1 from @ClientSites where ClientSitesID is null)
        set @ClientUsersID = null;


    select VW.*
    from 
    vwEmailTemplateDataSources VW 
    left outer join DataSources DS on DS.DataSourceID = VW.DataSourceID
    left outer join EmailList EL on EL.ID = DS.ImportListID
    left outer join @ClientSites CS on CS.ClientSitesID = EL.ClientSitesID
    WHERE NOT EXISTS (SELECT NULL FROM dbo.fnCSVIDsToTable(@ExcludeBackOfficeSystem) WHERE ID = VW.BackOfficeSystemID)            
    and VW.DataSourceID <> 320 -- exclude marketing templates
    and (@ClientUsersID is null or EL.ClientSitesID is null or CS.ClientSitesID is not null)
    ORDER BY VW.DataSourceName, VW.BackOfficeSystemId;