USP_MARKINGCOLUMN_MOVE

CREATE procedure dbo.USP_MARKINGCOLUMN_MOVE
    (
        @ID uniqueidentifier,
        @SEQUENCECHANGE int = 1,
        @CHANGEAGENTID uniqueidentifier = null
    )
as
begin
    -- See if we are making any change at all

    if (@SEQUENCECHANGE <> 0)
    begin
        -- Get the original sequence for the item

        declare @ORGSEQUENCE int;
        declare @MCSETID uniqueidentifier;

        select 
            @ORGSEQUENCE = SEQUENCE,
            @MCSETID = MARKINGCOLUMNSETID 
        from dbo.MARKINGCOLUMN 
        where ID = @ID;


    -- get the next higher/lower item's SEQUENCE

    declare @SWAPSEQUENCE int;
    select 
        @SWAPSEQUENCE = case when @SEQUENCECHANGE < 0 then max(SEQUENCE) else min(SEQUENCE) end
    from  dbo.MARKINGCOLUMN 
    where 
        MARKINGCOLUMNSETID = @MCSETID and 
        ((@SEQUENCECHANGE > 0 and SEQUENCE > @ORGSEQUENCE) 
            or (@SEQUENCECHANGE < 0 and SEQUENCE < @ORGSEQUENCE))


    if @SWAPSEQUENCE is not null
        -- handle both in single update due to possible uniqueness constraints

        update dbo.MARKINGCOLUMN
            set SEQUENCE = 
                case when ID = @ID 
                    then @SWAPSEQUENCE 
                    else @ORGSEQUENCE 
                end,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = getdate()
        where
            MARKINGCOLUMNSETID = @MCSETID and SEQUENCE in (@ORGSEQUENCE, @SWAPSEQUENCE)

    end

    return 0;
end