USP_DATALIST_EVENTCOPYWEEKSTARTDATEOFEVENT

CREATE procedure dbo.USP_DATALIST_EVENTCOPYWEEKSTARTDATEOFEVENT
(
    @EVENTID uniqueidentifier
)
as
    set nocount on;

    declare @DATETOCOPY datetime;
    declare @FROMDATE datetime;
    declare @TODATE datetime;

    select @DATETOCOPY = EVENT.STARTDATE from dbo.EVENT where EVENT.ID = @EVENTID;
    set @FROMDATE = dbo.UFN_DATE_THISWEEK_FIRSTDAY(@DATETOCOPY, 0);
    set @TODATE = dbo.UFN_DATE_THISWEEK_LASTDAY(@DATETOCOPY, 1);

    -- Making this query dynamic so that @FROMDATE and @TODATE can be parameterized

    -- and the query engine will use the STARTDATE index

    declare @SQL nvarchar(max) = '
        select
            EVENT.ID as EVENTID,
            EVENT.NAME,
            EVENT.STARTDATETIME as START,
            EVENT.ENDDATETIME as [END],
            EVENT.DESCRIPTION,
            EVENT.CAPACITY,
            EVENT.PROGRAMID,
            PROGRAM.NAME,
            dbo.UFN_EVENT_GETSEQUENCEDLOCATIONS_TOITEMLISTXML(EVENT.ID) as LOCATIONS,
            dbo.UFN_EVENTRESOURCE_GETRESOURCES_TOITEMLISTXML(EVENT.ID) as RESOURCES,
            dbo.UFN_EVENTSTAFFRESOURCE_GETRESOURCES_TOITEMLISTXML(EVENT.ID) as STAFFRESOURCES,
            datepart(weekday, EVENT.STARTDATE) as DAYOFWEEK,
            dbo.UFN_EVENT_GETEVENTPRICES_TOITEMLISTXML(EVENT.ID) as PRICES,
            dbo.UFN_PROGRAMEVENTPREFERENCEGROUPS_GETPREFERENCEGROUPS_TOITEMLISTXML(EVENT.ID) as PREFERENCEGROUPS,
            EVENT.SUPERCEDESPROGRAMPUBLICDESCRIPTION,
            EVENT.PUBLICDESCRIPTIONTEXT,
            EVENT.PUBLICDESCRIPTIONHTML
        from
            dbo.EVENT
        inner join
            dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
        where
            EVENT.STARTDATE between @FROMDATE and @TODATE and
            PROGRAM.ISACTIVE = 1
        order by
            EVENT.STARTDATETIME asc
    ';

    exec sp_executesql @SQL, N'@FROMDATE datetime, @TODATE datetime', @FROMDATE = @FROMDATE, @TODATE = @TODATE;

    return 0;