SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE testdata(Name VARCHAR(100), [Order] INT);
INSERT INTO testdata VALUES
('Line 1', 1),
('Line 2', 2),
('Line 3', 3),
('Line 4', 3),
('Line 5', 7),
('Line 6', 8);
6 rows affected

DECLARE @OLDPOS INT = 1;
DECLARE @NEWPOS INT = 8;
SELECT *, ROW_NUMBER() OVER (ORDER BY [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END, Name) AS [1 -> 8]
FROM testdata ORDER BY 3


Name
Order
1 -> 8


Line 2
2
1

Line 3
3
2

Line 4
3
3

Line 5
7
4

Line 6
8
5

Line 1
1
6


DECLARE @OLDPOS INT = 3;
DECLARE @NEWPOS INT = 8;
SELECT *, ROW_NUMBER() OVER (ORDER BY [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END, Name) AS [3 -> 8]
FROM testdata ORDER BY 3


Name
Order
3 -> 8


Line 1
1
1

Line 2
2
2

Line 5
7
3

Line 6
8
4

Line 3
3
5

Line 4
3
6


DECLARE @OLDPOS INT = 8;
DECLARE @NEWPOS INT = 1;
SELECT *, ROW_NUMBER() OVER (ORDER BY [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END, Name) AS [8 -> 1]
FROM testdata ORDER BY 3


Name
Order
8 -> 1


Line 6
8
1

Line 1
1
2

Line 2
2
3

Line 3
3
4

Line 4
3
5

Line 5
7
6


DECLARE @OLDPOS INT = 3;
DECLARE @NEWPOS INT = 1;
SELECT *, ROW_NUMBER() OVER (ORDER BY [Order] + CASE
  WHEN [Order] = @OLDPOS THEN @NEWPOS - @OLDPOS
  WHEN @NEWPOS > @OLDPOS AND [Order] > @OLDPOS AND [Order] <= @NEWPOS THEN -1
  WHEN @NEWPOS < @OLDPOS AND [Order] < @OLDPOS AND [Order] >= @NEWPOS THEN 1
  ELSE 0
END, Name) AS [3 -> 1]
FROM testdata ORDER BY 3


Name
Order
3 -> 1


Line 3
3
1

Line 4
3
2

Line 1
1
3

Line 2
2
4

Line 5
7
5

Line 6
8
6