spLogPageHit_BULK

CREATE PROCEDURE [dbo].[spLogPageHit_BULK] (@XML as xml) AS

    DECLARE @idoc int
    DECLARE @Hit Table (
        [CLID] int,
        [CSID] int,
        [UID] int,
        [SID] nvarchar(64),
        [RQDT] datetime,
        [PGID] int,
        [SRID] int,
        [STID] int,
        [TCPIP] nvarchar(30),
        [RURL] ntext,
        [REID] int,
        [EJRID] int,
        [FRSID] int,
        [FRTID] int,
        [URL] nvarchar(1784),
        [UA] nvarchar(256),
        [GUID] nchar(36),
        [UPPGID] int,
        [CCPRELID] int,
        [DID] int
    )

    EXEC sp_xml_preparedocument @idoc OUTPUT, @XML

    INSERT INTO @Hit (
        [CLID],
        [CSID],
        [UID],
        [SID],
        [RQDT],
        [PGID],
        [SRID],
        [STID],
        [TCPIP],
        [RURL],
        [REID],
        [EJRID],
        [FRSID],
        [FRTID],
        [URL],
        [UA],
        [GUID],
        [UPPGID],
        [CCPRELID],
        [DID]
    )
    SELECT
        [CLID],
        [CSID],
        [UID],
        [SID],
        [RQDT],
        [PGID],
        [SRID],
        [STID],
        [TCPIP],
        [RURL],
        [REID] ,
        [EJRID],
        [FRSID],
        [FRTID],
        [URL],
        [UA],
        [GUID],
        [UPPGID],
        [CCPRELID],
        [DID]
    FROM OPENXML (@idoc, '/PageHitList/H', 2)
    WITH (
        [CLID] int,
        [CSID] int,
        [UID] int,
        [SID] nvarchar(64),
        [RQDT] datetime,
        [PGID] int,
        [SRID] int,
        [STID] int,
        [TCPIP] nvarchar(30),
        [RURL] ntext,
        [REID] int,
        [EJRID] int,
        [FRSID] int,
        [FRTID] int,
        [URL] nvarchar(1784),
        [UA] nvarchar(256),
        [GUID] nchar(36),
        [UPPGID] int,
        [CCPRELID] int,
        [DID] int
    )

    EXEC sp_xml_removedocument @idoc

    INSERT INTO Stats
    (
    ClientsID,
    ClientSitesID,
    userID,
    SessionID,
    RequestDate,
    PageID,
    SourceID,
    SourceTypeID,
    TCPIPAddress,
    ReferringPageURL,
    SourceREID,
    EmailJobRecipientID,
    FRSolicitorID,
    FRTeamID,
    URL,
    UserAgent,
    GUID,
    UserPersonalPageID,
    CCPRELID,
    DocumentID
    )

    SELECT
    [CLID],
    [CSID],
    [UID],
    [SID],
    [RQDT],
    [PGID],
    [SRID],
    [STID],
    [TCPIP],
    [RURL],
    [REID] ,
    [EJRID],
    [FRSID],
    [FRTID],
    [URL],
    [UA],
    [GUID],
    [UPPGID],
    [CCPRELID],
    [DID]
    FROM @Hit

    if exists (select * from @Hit where not [SRID] is null and [STID]=1)
        UPDATE Email_Recipient SET ClickedThrough = 1 
        FROM Email_Recipient ER inner join @Hit t on er.userid = t.[UID] and er.EmailID = t.[SRID] and t.[STID]=1


    if exists (select * from @Hit where not [UID] is null )
        UPDATE NetAccount SET OnlineStatus = 1, [OnlineStatusUpdateDate] = getUTCDate()
        FROM NetAccount NA inner join @Hit t on NA.ClientUsersID = t.[UID] 
        WHERE NA.ClientUsersID IS NOT NULL


    UPDATE NetAccount SET OnlineStatus = 0, [OnlineStatusUpdateDate] = NULL, UpdateDate = getUTCDate()
    WHERE OnlineStatus=1 AND
    DATEDIFF(MINUTE, dateadd(MINUTE,-20,getutcdate()),  OnlineStatusUpdateDate) < 0