>Something like this:
>
>DECLARE @TestOrders TABLE ([Order] int, OldOrder int)
>
>insert into @TestOrders values (1,1)
>insert into @TestOrders values (2,2)
>insert into @TestOrders values (3,3)
>insert into @TestOrders values (4,4)
>insert into @TestOrders values (5,5)
>insert into @TestOrders values (6,6)
>insert into @TestOrders values (7,7)
>insert into @TestOrders values (8,8)
>insert into @TestOrders values (9,9)
>
>
>declare @OriginalOrder int
>declare @NewOrder int
>
>set @OriginalOrder = 2
>set @NewOrder = 5
>
>UPDATE @TestOrders SET [ORDER] = CASE WHEN [Order] = @OriginalOrder
> THEN @NewOrder
> WHEN [Order] <= @NewOrder AND @OriginalOrder > @NewOrder AND [Order] > @OriginalOrder
> THEN [ORDER] +1
> WHEN [Order] <= @NewOrder AND @NewOrder > @OriginalOrder AND [Order] > @OriginalOrder
> THEN [ORDER] -1
> ELSE [ORDER] END
>select * from @TestOrders ORDER BY [Order]
>
>set @OriginalOrder = 9
>set @NewOrder = 2
>
>UPDATE @TestOrders SET [ORDER] = CASE WHEN [Order] = @OriginalOrder
> THEN @NewOrder
> WHEN [Order] <= @NewOrder AND @OriginalOrder > @NewOrder AND [Order] > @OriginalOrder
> THEN [ORDER] +1
> WHEN [Order] <= @NewOrder AND @NewOrder > @OriginalOrder AND [Order] > @OriginalOrder
> THEN [ORDER] -1
> ELSE [ORDER] END
>select * from @TestOrders ORDER BY [Order]
>
>
Seems to work great, thanks.
If it's not broken, fix it until it is.
My Blog 
