Fetch from sql tables using loop

DECLARE @lessons TABLE(LessonID INT IDENTITY(1,1), LessonName VARCHAR(30))

INSERT INTO @lessons (LessonName)
SELECT 'lesson A'
UNION ALL SELECT 'lesson B'
UNION ALL SELECT 'lesson C'
UNION ALL SELECT 'lesson D'
--UNION ALL SELECT 'lesson E'


DECLARE @subject_lesson TABLE (slid INT IDENTITY(1,1), lids VARCHAR(30))

INSERT INTO @subject_lesson (lids)
SELECT '1,2,3'
UNION ALL SELECT '2,3,4'
UNION ALL SELECT '1,3,5'

;WITH Lessons AS
(
	SELECT slid, CONVERT(INT, LEFT(lids, CHARINDEX(',', lids)-1)) AS lid, RIGHT(lids, LEN(lids) - CHARINDEX(',', lids)) AS Remainder
	FROM @subject_lesson
	WHERE CHARINDEX(',', lids)>0
	UNION ALL 
	SELECT slid, CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS lid, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
	FROM Lessons
	WHERE CHARINDEX(',', Remainder)>0
	UNION ALL
	SELECT slid, CONVERT(INT, Remainder) AS Remainder, NULL AS Remainder
	FROM Lessons
	WHERE CHARINDEX(',', Remainder)=0
)
SELECT L.slid, L.lid, M.LessonName
FROM Lessons AS L LEFT JOIN @lessons AS M ON L.lid = M.LessonID
ORDER BY L.slid, L.lid

slid   id  LessonName
1      1   lesson A
1      2   lesson B
1      3   lesson C
2      2   lesson B
2      3   lesson C
2      4   lesson D
3      1   lesson A
3      3   lesson C
3      5   NULL