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 <> 0
that 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