SQL Server - Sort Order Templates

• Download scripts - 4.81 KB

Introduction

Basically, whenever I insert/update/delete rows and they have a sort-order column, they need to be re-sequenced. It seems that there should be an easy template for doing this. I couldn't find a great way to do this with triggers, so I did it as code snippets to place at the beginning of my CRUD Stored Procedures.

Now, of course, the whole point is for it to work on new tables, so I extracted out the following SSMS Script Templates (see attached zip).

OK, so I put the different templates in the zip. You'll press Ctrl-Shift-M, and it'll ask you for the table name, ID, parent ID, IsActive, and sort-order columns. I used separate templates depending on whether you have an IsActive column/expression and if you have a parent ID.

OK, now if you're like me and have an IsActive bit field, then you can use the IsActive column scripts. If you're not like me, and use an IsDeleted field or just a DeletedDate field, then you'll want to use the IsActive expression scripts, with an "IsActive expression" of "IsDeleted=0" or "DeletedDate is null" (hope some of that makes sense).

Using the code

Here's a simple example of what the generated code looks like when the item has a parent item and does not have an IsActive flag/expression. This example is for a ListItem, and it assumes a 1:N relationship between the List and the ListItem, where the ListItem has a sort-order.

-----------------------------
--Put at top of Update proc
--Expected Parameters: @ListItemId, @ListId, @SortOrder
-----------------------------
declare @oldSortOrder int
select @oldSortOrder=SortOrder from ListItem where ListItemId = @ListItemId

if @oldSortOrder < @SortOrder
begin
    update ListItem
    set SortOrder=SortOrder-1
    where ListId= @ListId and SortOrder between @oldSortOrder and 
                  @SortOrder and ListItemId <> @ListItemId
end
else
begin
    update ListItem
    set SortOrder=SortOrder+1
    where ListId= @ListId and SortOrder between @SortOrder and 
                  @oldSortOrder and ListItemId <> @ListItemId
end

-----------------------------
--Put at top of Create proc
--Expected Parameters: @ListId,@SortOrder
-----------------------------
update ListItem
set SortOrder=SortOrder+1
where ListId= @ListId and SortOrder >= @SortOrder

-----------------------------
--Put at top of Delete proc
--Expected Parameters: @ListItemId
-----------------------------
declare @oldSortOrder int
declare @ListId int
select @oldSortOrder=SortOrder,ListId=@ListId from 
                     ListItem where ListItemId = @ListItemId

update ListItem
set SortOrder=SortOrder-1
where ListId= @ListId and SortOrder > @oldSortOrder

---------------------------------------------------------------
--ReSequenceGroup----Worried About Other Processes making Changes? 
--Expected Parameters: @ListId
---------------------------------------------------------------
declare @tempTable table (newSortOrder int identity(1,1), Id int)

insert into @tempTable 
    (ID)
select ListItemId
from ListItem
where ListId = @ListId
order by SortOrder,Name

update ListItem
set SortOrder = newSortOrder
from 
    ListItem 
    inner join @tempTable t 
        on ListItem.ListItemId = t.Id

---------------------------------------------------------------
--ReSequenceAll----Worried About Other Processes making Changes? 
--Expected Parameters: 
---------------------------------------------------------------
declare @tempTable table (newSortOrder int identity(1,1), Id int, 
                          ParentId int, SortMinus int)

insert into @tempTable 
    (ID,ParentId)
select ListItemId,ListId
from ListItem
order by ListId,SortOrder,Name

update @tempTable
set SortMinus = minSort - 1
from @tempTable t 
    inner join (select ParentId, min(newSortOrder) as minSort 
                from @tempTable group by ParentId) subQuery
        t.ParentId = subQuery.ParentId

update ListItem
set SortOrder = newSortOrder - SortMinus
from 
    ListItem 
    inner join @tempTable t 
        on ListItem.ListItemId = t.Id