USP_GRADELEVEL_MOVE

CREATE procedure dbo.USP_GRADELEVEL_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;
        select @ORGSEQUENCE = SEQUENCE from dbo.GRADELEVEL 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.GRADELEVEL 
        where 
            ((@SEQUENCECHANGE > 0 and SEQUENCE > @ORGSEQUENCE) 
                or (@SEQUENCECHANGE < 0 and SEQUENCE < @ORGSEQUENCE))


        if @SWAPSEQUENCE is not null
            update dbo.GRADELEVEL
                set SEQUENCE =
                    case when ID = @ID  
                        then @SWAPSEQUENCE
                        else @ORGSEQUENCE
                    end,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = getdate()
            where SEQUENCE in (@ORGSEQUENCE, @SWAPSEQUENCE)
    end

    return 0;
end