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', 4);
4 rows affected

DECLARE @OLDPOS INT = 1;
DECLARE @NEWPOS INT = 4;
SELECT *, [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 AS [1 -> 4]
FROM testdata ORDER BY 3


Name
Order
1 -> 4


Line 2
2
1

Line 3
3
2

Line 4
4
3

Line 1
1
4


DECLARE @OLDPOS INT = 2;
DECLARE @NEWPOS INT = 4;
SELECT *, [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 AS [2 -> 4]
FROM testdata ORDER BY 3


Name
Order
2 -> 4


Line 1
1
1

Line 3
3
2

Line 4
4
3

Line 2
2
4


DECLARE @OLDPOS INT = 4;
DECLARE @NEWPOS INT = 1;
SELECT *, [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 AS [4 -> 1]
FROM testdata ORDER BY 3


Name
Order
4 -> 1


Line 4
4
1

Line 1
1
2

Line 2
2
3

Line 3
3
4


DECLARE @OLDPOS INT = 3;
DECLARE @NEWPOS INT = 1;
SELECT *, [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 AS [3 -> 1]
FROM testdata ORDER BY 3


Name
Order
3 -> 1


Line 3
3
1

Line 1
1
2

Line 2
2
3

Line 4
4
4