Looks wrong, if you go more than 1 above the maximum char number then it will loop through again.

if you change the WHILE statement from while (@counter < @Occurrence)

to while (@counter < @Occurrence) and @pos <> 0that should fix the issue

Try running below

DECLARE @TXT varchar(255)

SET @TXT = 'Michael Williamson'

SELECT dbo.CHARINDEX2('l',@TXT,1)

SELECT dbo.CHARINDEX2('l',@TXT,2)

SELECT dbo.CHARINDEX2('l',@TXT,4)

SELECT dbo.CHARINDEX2('l',@TXT,6)

Hope that helps