USP_BUSINESSPROCESSRUNTIMEINFO_INSERTORUPDATE

CREATE procedure dbo.USP_BUSINESSPROCESSRUNTIMEINFO_INSERTORUPDATE
(
    @BUSINESSPROCESSSTATUSID uniqueidentifier,
    @BUSINESSPROCESSID uniqueidentifier,
    @ROOTREQUESTCORRELATIONID uniqueidentifier,
    @SPID smallint = null
)
as
begin

  declare @LOGINTIME datetime;

  set @SPID = coalesce(@SPID, @@spid);

  -- SQL Snap matches BUSINESSPROCESSRUNTIMEINFO.LOGINTIME (which we set here) and sys.dm_exec_sessions.login_time to filter out stale

  --  rows from BUSINESSPROCESSRUNTIMEINFO. If the login times don't match for a given SPID, then SQL Snap knows the row is stale and 

  --  ignores that row's business process runtime info. Note that sys.dm_exec_sessions.login_time represents the time when the session

  --  was established.

  select @LOGINTIME = login_time from sys.dm_exec_sessions where session_id = @SPID;

  -- If a SPID was passed in that does not exist in sys.dm_exec_sessions, use a default datetime that will never match any sys.dm_exec_sessions.login_time

  set @LOGINTIME = coalesce(@LOGINTIME, cast(0 AS datetime));

  -- If some row already contains our SPID, then update that row so it points to our business process runtime info.

  -- Otherwise, insert a new row that contains our SPID and points to our business process runtime info.

  -- Since SQL frequently reuses SPIDs, this ensures our SPID column stays unique.

  update dbo.BUSINESSPROCESSRUNTIMEINFO set 
    BUSINESSPROCESSSTATUSID = @BUSINESSPROCESSSTATUSID, 
    BUSINESSPROCESSID = @BUSINESSPROCESSID, 
    ROOTREQUESTCORRELATIONID = @ROOTREQUESTCORRELATIONID, 
    LOGINTIME = @LOGINTIME 
  where SPID = @SPID;

  if @@rowcount = 0
    begin
      insert into dbo.BUSINESSPROCESSRUNTIMEINFO 
      (
        BUSINESSPROCESSID, 
        BUSINESSPROCESSSTATUSID, 
        SPID, 
        ROOTREQUESTCORRELATIONID, 
        LOGINTIME 
      ) values (
        @BUSINESSPROCESSID, 
        @BUSINESSPROCESSSTATUSID, 
        @SPID, 
        @ROOTREQUESTCORRELATIONID, 
        @LOGINTIME 
      );
    end
  return 0;
end