Change Squience Logic

Create PROCEDURE usp_MoveSequence]

@ID int,

@Sequence int,

@MoveBy varChar(4)

AS

BEGIN—PROCEDURE

SET NOCOUNT ON;

DECLARE @compateTo INT

DECLARE @compateToMax INT

DECLARE @currentSequence INT

DECLARE @NextSequence INT

DECLARE @NextRecordID INT

IF @MoveBy='UP'

Select TOP 1 @NextSequence=Sequence, @NextRecordID=ID from TableName where sequence<@Sequence ORDER BY sequence DESC

ELSE IF @MoveBy='DOWN'

Select TOP 1 @NextSequence=Sequence, @NextRecordID=ID from TableName where sequence>@Sequence ORDER BY sequence

--ENDIF

IF @NextRecordID<>0

BEGIN

UPDATE TableName SET Sequence=@NextSequence WHERE ID=@ID

UPDATE TableName SET Sequence=@Sequence WHERE ID=@NextRecordID

END

END--PROCEDURE