Usman ur Rehman Ahmed's blog

The stored procedures below can usually be used to swap two records fulfilling a given criteria.Note that the swapping will work even if some records between these two have been deleted. i.e. the monotonic sequence 0, 1, 2 ... is not strictly followed. (e.g. 0, 1, 3, 6, 7...)

The sample use demonstrated here is to swap the Display Priority of two recrods from the table ProjectsPerTab. Such a situation is often required in sorting item by item or giving precedence to one item over other as shown below,

- pk (Primary key - int)

- TabId (Foreign Key from DNN Tabs table - int)

- ProjectFK (Projects table foreign key - int)

- DisplayPriority (int)

Move Down (Increase Display Priority/Swap with next record)

create procedure {databaseOwner}{objectQualifier}IncreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Exchange display priority with next one

            declare @pk int
            declare @DisplayPriority int

            -- Select minimum primary key for this tab with display priority greater than that of current project
            select @pk = min(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority > @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            if @pk is not null
            begin
                -- Exchange display priorities
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where pk = @pk
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end

    end

GO

Move Up (Decrease Display Priority/Swap with previous record)

create procedure {databaseOwner}{objectQualifier}DecreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
        -- Step 1: Retrieve display priority of this project and store that since that will be the last one to be updated

            declare @CurrentProjectDisplayPriority int
            select @CurrentProjectDisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where TabId = @TabId and ProjectFK = @ProjectFK

        -- Step 2: Exchange display priority with previous one

            declare @pk int
            declare @DisplayPriority int

            -- Select maximum primary key for this tab with display priority less than that of current project
            select @pk = max(pk) from {objectQualifier}ProjectsPerTab where TabId = @TabId and DisplayPriority < @CurrentProjectDisplayPriority
            select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk

            if @pk is not null
            begin

                -- Exchange display priorities
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where pk = @pk
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end

    end

GO