USP_STUDENTSKILLCOURSE_MOVE

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

    if (@SEQUENCECHANGE <> 0)
    begin

        if @CHANGEAGENTID is null  
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

        declare @CURRENTDATE datetime = getdate();

        declare @COURSEID uniqueidentifier;
        -- Get the original sequence for the item

        declare @ORGSEQUENCE int;
        select 
            @ORGSEQUENCE = SEQUENCE, 
            @COURSEID = COURSEID 
        from dbo.STUDENTSKILLCOURSE 
        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.STUDENTSKILLCOURSE 
        where 
            COURSEID = @COURSEID and 
            ((@SEQUENCECHANGE > 0 and SEQUENCE > @ORGSEQUENCE) 
                or (@SEQUENCECHANGE < 0 and SEQUENCE < @ORGSEQUENCE))


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

            update dbo.STUDENTSKILLCOURSE
                set SEQUENCE = 
                    case when ID = @ID 
                        then @SWAPSEQUENCE 
                        else @ORGSEQUENCE 
                    end,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
            where
                COURSEID = @COURSEID and SEQUENCE in (@ORGSEQUENCE, @SWAPSEQUENCE)

    end

    return 0;
end