USP_EMAILCATEGORYPRIORITY_DECREASE_UPDATE

Executes the "Decrease Email Category Priority" record operation.

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	Input parameter indicating the ID of the record being updated.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the update.

Definition

Copy



CREATE procedure dbo.USP_EMAILCATEGORYPRIORITY_DECREASE_UPDATE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier
)
as 
begin
  set nocount on;

  begin try

    if @CHANGEAGENTID is null  
      exec dbo.[USP_CHANGEAGENT_GETORCREATECHANGEAGENT] @CHANGEAGENTID output;

    declare @MAXPRIORITY integer;
    declare @CURRENTPRIORITY integer;

    select @MAXPRIORITY = max(PRIORITY) from dbo.[EMAILCATEGORYPRIORITY];

    select @CURRENTPRIORITY = PRIORITY from dbo.[EMAILCATEGORYPRIORITY] where ID = @ID;

    if (@CURRENTPRIORITY < @MAXPRIORITY)
    begin
      -- swap priorities with item below

      declare @NEXTPRIORITY integer = @CURRENTPRIORITY + 1;
      begin
        update dbo.[EMAILCATEGORYPRIORITY] set PRIORITY = @CURRENTPRIORITY, CHANGEDBYID = @CHANGEAGENTID where PRIORITY = @NEXTPRIORITY;
        update dbo.[EMAILCATEGORYPRIORITY] set PRIORITY = @NEXTPRIORITY, CHANGEDBYID = @CHANGEAGENTID where ID = @ID;
      end

    end

  end try
  begin catch
    exec dbo.[USP_RAISE_ERROR];
    return 1;
  end catch

end