Sql Server - Sort Order Templates

• Download scripts.zip - 4.82 KB

Introduction  

Basically,  whenever I insert/update/delete rows and they have a sort-order column, they need to be resequenced. 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 your 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, is active , 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 ListItem, and it assumes a 1:N relationship between List and ListItem, where 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

Points of Interest 

History