USP_USERSETTINGSSEARCHLISTTASKHISTORY_CREATEORUPDATE

Creates or updates a search list task history record.

Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@APPUSERID	uniqueidentifier	IN
@SEARCHLISTCATALOGID	uniqueidentifier	IN
@TASKCATALOGID	uniqueidentifier	IN
@PARAMETERS	xml	IN

Definition

Copy



CREATE procedure dbo.USP_USERSETTINGSSEARCHLISTTASKHISTORY_CREATEORUPDATE(
    @ID uniqueidentifier = null output,
    @APPUSERID uniqueidentifier,
    @SEARCHLISTCATALOGID uniqueidentifier,
    @TASKCATALOGID uniqueidentifier,
    @PARAMETERS xml)
as
begin

    declare @NOW datetime;

    declare @EXISTINGID uniqueidentifier;

    select @EXISTINGID = ID from dbo.USERSETTINGSSEARCHLISTTASKHISTORY 
    where APPUSERID = @APPUSERID 
        and SEARCHLISTCATALOGID = @SEARCHLISTCATALOGID
        and TASKCATALOGID = @TASKCATALOGID;

    set @NOW = getdate();

    if @EXISTINGID is null
    begin

        if @ID is null
        begin
            set @ID = newid();
        end;

        insert into dbo.USERSETTINGSSEARCHLISTTASKHISTORY (
            ID,
            APPUSERID,
            SEARCHLISTCATALOGID,
            TASKCATALOGID,
            PARAMETERS,
            DATECHANGED)
        values (
            @ID,
            @APPUSERID,
            @SEARCHLISTCATALOGID,
            @TASKCATALOGID,
            @PARAMETERS,
            @NOW);

        -- Only keep the last 5 items.

        delete from dbo.USERSETTINGSSEARCHLISTTASKHISTORY 
        where APPUSERID = @APPUSERID and ID not in
            (select top(5) ID 
            from dbo.USERSETTINGSSEARCHLISTTASKHISTORY 
            where APPUSERID = @APPUSERID
            order by DATECHANGED desc);

    end
    else
    begin

        set @ID = @EXISTINGID;

        update dbo.USERSETTINGSSEARCHLISTTASKHISTORY 
        set PARAMETERS = @PARAMETERS,
            DATECHANGED = @NOW
        where ID = @EXISTINGID;

    end;

end;