USP_DATALIST_EVENTWIDGET

CREATE procedure dbo.USP_DATALIST_EVENTWIDGET
                (    @EVENTID uniqueidentifier, 
                    @ROLECODE tinyint, 
                    @WIDGETID int = NULL
                )with execute as caller
as
    set nocount on;

    declare @CHANGEAGENTID uniqueidentifier 
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

    declare @CURRENTDATE datetime
    set @CURRENTDATE = getdate()

    -- Load the missing defaults into the EventWidget table

    INSERT INTO EVENTWIDGET (ID,widgetid,eventid,displaytext,rolecode,isactive,addedbyid,changedbyid,dateadded,datechanged)
    SELECT NEWID(),WG.WIDGETID,@EVENTID,WG.DEFAULTTEXT,@ROLECODE,1,@CHANGEAGENTID,@CHANGEAGENTID,@CURRENTDATE,@CURRENTDATE
    FROM dbo.WIDGETGALLERY WG
    LEFT OUTER JOIN dbo.EVENTWIDGET EW
    ON WG.WIDGETID = EW.WIDGETID
    AND EW.EVENTID = @EVENTID
    AND EW.ROLECODE = @ROLECODE
    WHERE EW.WIDGETID IS NULL
  AND WG.ISACTIVE = 1


    -- Get the main widgets

    IF @WIDGETID IS NULL
        SELECT EW.ID,
               EW.WIDGETID,
               WG.NAME,
               WG.DESCRIPTION,
               EW.DISPLAYTEXT,
               EW.ISACTIVE,
               WG.PARENTWIDGETID,
               EW.ALLOWEDIT,
               EW.ALLOWMOVE,
               EW.ALLOWREMOVE
        FROM dbo.EVENTWIDGET EW WITH (NOLOCK)
        INNER JOIN dbo.WIDGETGALLERY WG WITH (NOLOCK)
        ON WG.WIDGETID = EW.WIDGETID
        WHERE EW.EVENTID = @EVENTID
        AND EW.ROLECODE = @ROLECODE
    -- return all event widget data so front end can cache it at event level to improve performance

    -- the filtering will be done on the front end

        --AND WG.PARENTWIDGETID IS NULL  


    ELSE    -- Get the Widget and Child widgets


        SELECT EW.ID,
               EW.WIDGETID,
               WG.NAME,
               WG.DESCRIPTION,
               EW.DISPLAYTEXT,
               EW.ISACTIVE,
               WG.PARENTWIDGETID,
               EW.ALLOWEDIT,
               EW.ALLOWMOVE,
               EW.ALLOWREMOVE

        FROM dbo.EVENTWIDGET EW WITH (NOLOCK)
        INNER JOIN dbo.WIDGETGALLERY WG WITH (NOLOCK)
        ON WG.WIDGETID = EW.WIDGETID
        WHERE EW.EVENTID = @EVENTID
        AND EW.ROLECODE = @ROLECODE
        AND (EW.WIDGETID = @WIDGETID OR WG.PARENTWIDGETID = @WIDGETID)