SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--Small sample set
CREATE TABLE dbo.Playlist (SongID int,
                           PlaylistID int,
                           Position int);
INSERT INTO dbo.Playlist (SongID,
                      PlaylistID,
                      Position)
VALUES(1,1,1),
      (3,1,2),
      (5,1,3),
      (2,1,4),
      (2,2,1),
      (10,2,2);


--Check data
SELECT *
FROM dbo.Playlist;


SongID
PlaylistID
Position


1
1
1

3
1
2

5
1
3

2
1
4

2
2
1

10
2
2


--CREATE the SP
CREATE PROC dbo.MoveSongPosition @PlaylistID int,
                             @SongID int,
                             @NewPosition int
AS BEGIN

    UPDATE P
    SET Position = CASE WHEN SongID = @SongID THEN @NewPosition ELSE Position +1 END
    FROM dbo.Playlist P
    WHERE P.PlaylistID = @PlaylistID
      AND (P.SongID = @SongID
       OR  P.Position BETWEEN @NewPosition AND (SELECT sq.Position
                                               FROM dbo.Playlist sq
                                               WHERE sq.SongID = @SongID
                                                 AND sq.PlaylistID = @PlaylistID));
END
--Run and test the SP
EXEC dbo.MoveSongPosition @PlaylistID = 1,
                          @SongID = 2,
                          @NewPosition = 2;


--Check the new data
SELECT *
FROM dbo.Playlist;


SongID
PlaylistID
Position


1
1
1

3
1
3

5
1
4

2
1
2

2
2
1

10
2
2


--Clean up
DROP PROC dbo.MoveSongPosition;
DROP TABLE dbo.Playlist;