SQL Stored Procedure: Reordering rows after delete
DECLARE @TaskID int
DECLARE @SubTaskPosition int

DECLARE curSubTaskPositionUpdate cursor fast_forward
FOR
    SELECT TaskID, SubTaskPosition
    FROM TaskDetails
    WHERE SubTaskID = @SubTaskID
OPEN curSubTaskPositionUpdate
FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE TaskDetails
        SET SubTaskPosition = SubTaskPosition - 1
        WHERE TaskID = @TaskID
        AND SubTaskPosition > @SubTaskPosition

        FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
    END
CLOSE curSubTaskPositionUpdate
DEALLOCATE curSubTaskPositionUpdate

DECLARE @TaskID uniqueidentifier
DECLARE @SubTaskPosition int

DECLARE curSubTaskPositionUpdate cursor fast_forward
FOR
    SELECT TaskID, SubTaskPosition
    FROM TaskDetails
    WHERE SubTaskID = @subTaskID
OPEN curSubTaskPositionUpdate
FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
WHILE @@FETCH_STATUS = 0
   BEGIN
-- Delete the subTask
DELETE FROM TaskDetails
WHERE TaskID = @TaskID
AND SubTaskPosition = @SubTaskPosition

-- Update the other subTasks
        UPDATE TaskDetails
        SET SubTaskPosition = SubTaskPosition - 1
        WHERE TaskID = @TaskID
        AND SubTaskPosition > @SubTaskPosition

        FETCH NEXT FROM curSubTaskPositionUpdate INTO @TaskID, @SubTaskPosition
    END
CLOSE curSubTaskPositionUpdate
DEALLOCATE curSubTaskPositionUpdate

UPDATE  TaskDetails
SET     subTaskPosition = ROW_NUMBER() OVER(PARTITION BY TaskID  ORDER BY subTaskPosition)
WHERE   TaskID IN(  SELECT  s.TaskID
                    FROM    TaskDetails s
                    WHERE   SubTaskID = @subTaskID)

;WITH
  cteRows As
(
    SELECT  *,
    ROW_NUMBER() OVER(PARTITION BY TaskID  ORDER BY subTaskPosition) As NewPosition
    FROM    TaskDetails
)
UPDATE  cteRows
SET     subTaskPosition = NewPosition
WHERE   TaskID IN(  SELECT  s.TaskID
                    FROM    TaskDetails s
                    WHERE   SubTaskID = @subTaskID)

declare @pos int
select @pos = subTaskPosition from TaskDetails where SubTaskID = @subTaskID
delete ...
delete ...
update TaskDetails set subTaskPosition = subTaskPosition + 1 where SubTaskID = @subTaskID and subTaskPosition > @pos