set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[SetTaskPosition]
@TaskID uniqueidentifier,
@Position int
AS
DECLARE @ListID uniqueidentifier
DECLARE @OldPosition int
SELECT
@ListID = ListID, @OldPosition = ListOrder
FROM
Tasks
WHERE TaskID = @TaskID
IF @Position < @OldPosition
BEGIN
UPDATE Tasks
SET
ListOrder = ListOrder + 1
WHERE
ListID = @ListID AND ListOrder >= @Position AND ListOrder < @OldPosition
UPDATE TOP(1) Tasks
SET
ListOrder = @Position
WHERE
TaskID = @TaskID
END
ELSE IF @Position > @OldPosition
BEGIN
UPDATE Tasks
SET
ListOrder = ListOrder - 1
WHERE
ListID = @ListID AND ListOrder > @OldPosition AND ListOrder <= @Position
UPDATE TOP(1) Tasks
SET
ListOrder = @Position
WHERE
TaskID = @TaskID
END