USP_KPIINSTANCEHISTORY_DELETEBYDATERANGE

CREATE procedure dbo.USP_KPIINSTANCEHISTORY_DELETEBYDATERANGE(@KPIINSTANCEID uniqueidentifier,
    @STARTDATE datetime,
    @ENDDATE datetime,
    @CHANGEAGENTID uniqueidentifier,
  @TIMEPRECISION bit = 0)
as
    set nocount on;

    declare @e int;
    declare @contextCache varbinary(128);

    /* cache current context information */
    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID

  --if using time precision we may have several entries for a given day which we do not want to delete

    if @TIMEPRECISION = 0
  begin
        set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE) 
        set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(@ENDDATE);  
  end

    delete from dbo.KPIINSTANCEHISTORY   
    where KPIINSTANCEID = @KPIINSTANCEID  
     and ASOFDATE between @STARTDATE and @ENDDATE; 

    delete from dbo.KPIINSTANCESTATUSHISTORY
    where KPIINSTANCEID = @KPIINSTANCEID  
     and ASOFDATE between @STARTDATE and @ENDDATE; 

    /* reset CONTEXT_INFO to previous value */
    if not @contextCache is null
        set CONTEXT_INFO @contextCache

    select @e=@@error;

    if @e<>0 return -456; --always return non-zero sp result if an error occurs


    return 0;