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

Just a use of looping rocrds rather than using a cursor.

Increase Display Priority

This stored procedure will increase display priority field of provided 'project' plus all projects associated with this TabId and having display priority equivelent or greater than provided project.

create procedure {databaseOwner}{objectQualifier}IncreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
            Declare @ToUpdate bit
            set @ToUpdate = 0
        -- 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: Loop through all records which have a display priority greater than this project's display priority
        -- and increment by 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

            while @pk is not null
            begin
                set @ToUpdate = 1
                -- if display priority is null, set it equivelent to zero (0)
                if @DisplayPriority is null
                    begin
                        set @DisplayPriority = 0
                    end

                -- Increment display priority by one
                set @DisplayPriority = @DisplayPriority + 1

                -- Update display priority
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where pk = @pk

                -- Select next records for looping
                select @pk = min(pk) from {objectQualifier}ProjectsPerTab where pk > @pk AND TabId = @TabId
                select @DisplayPriority = DisplayPriority from {objectQualifier}tb_DNM_8_ProjectsPerTab where pk = @pk
            end

        -- Step 3: Increment display priority of this project by one (1)
        if @ToUpdate = 1
            begin
                set @CurrentProjectDisplayPriority = @CurrentProjectDisplayPriority + 1
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end
    end

GO

Decrease Display Priority

This stored procedure will decrease display priority field of provided 'project' plus all projects associated with this TabId and having display priority equivelent or less than provided project.

create procedure {databaseOwner}{objectQualifier}DecreaseProjectDisplayPriority
    @TabId int,
    @ProjectFK int
as
    begin
            Declare @ToUpdate bit
            set @ToUpdate = 0
        -- 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: Loop through all records which have a display priority less than this project's display priority
        -- and decrement by 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

            while @pk is not null
            begin
                set @ToUpdate = 1
                -- if display priority is null, set it equivelent to zero (0)
                if @DisplayPriority is null
                    begin
                        set @DisplayPriority = 0
                    end

                -- Increment display priority by one
                set @DisplayPriority = @DisplayPriority - 1

                -- Update display priority
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @DisplayPriority where pk = @pk

                -- Select next records for looping
                select @pk = max(pk) from {objectQualifier}ProjectsPerTab where pk < @pk AND TabId = @TabId
                select @DisplayPriority = DisplayPriority from {objectQualifier}ProjectsPerTab where pk = @pk
            end

        -- Step 3: Increment display priority of this project by one (1)
        if @ToUpdate = 1
            begin
                set @CurrentProjectDisplayPriority = @CurrentProjectDisplayPriority - 1
                update {objectQualifier}ProjectsPerTab set DisplayPriority = @CurrentProjectDisplayPriority where TabId = @TabId and ProjectFK = @ProjectFK
            end
    end

GO

I was playing with SQL server data-tier application today and I thought of few changes which would make a good addition in properties console,

1- Under Properties > Database.sqlsettings, I think it will be great if a tooltip is provided for each checkbox. Something like this,

2- Under Properties > ServerSelection.sqlpolicy, the dialog box that appears either requires input value or selection from drop down but relatively the notification that appears at the top of dialog box seems unusual,