Click here to Skip to main content
15,867,308 members
Articles / Database Development / SQL Server

SQL Server - Sort Order Templates

Rate me:
Please Sign up or sign in to vote.
0.00/5 (No votes)
11 Jun 2009CPOL1 min read 20.9K   51   9   3
Templates to fix changes in sort-order as they occur.

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.

SQL
-----------------------------
--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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
United States United States
likes boardgames, computer games, and enjoys his .net programming job.

Comments and Discussions

 
Generalanother way to re-sequence a column: row_number() Pin
jaan33us11-Jun-09 7:24
jaan33us11-Jun-09 7:24 
GeneralRe: another way to re-sequence a column: row_number() Pin
ColinBashBash11-Jun-09 7:45
ColinBashBash11-Jun-09 7:45 
GeneralRe: another way to re-sequence a column: row_number() Pin
ColinBashBash11-Jun-09 7:53
ColinBashBash11-Jun-09 7:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.