Mysql The stored procedure does not always work the same way

We have a specific stored procedure which splits one of the parameters received and performs some inserts based on the split data.

The procedure is working fine but randomly it crashes. We have an audit of the parameters being passed and also an audit of the values that have been split when the procedure was run. For some reason it seems like the split added an extra item at the beginning or sometimes mixes the order of the Split Data which matters a lot in our case as the data being split is formatted something like this UserId#LocationId#Note#RecordId*Date

The strange thing is that if we take the parameters from the audit and re-run the procedure that failed, it works fine!!! This is crashing once every 5000 times that it is run. The SplitString function is below.

ALTER FUNCTION [dbo].[SplitString]
(
    @string NVARCHAR(MAX),
    @delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX))
BEGIN
    DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
    WHILE @start < LEN(@string) + 1
    BEGIN
        IF @end = 0
            SET @end = LEN(@string) + 1  

        INSERT INTO @output (splitdata)
        VALUES(SUBSTRING(@string, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @string, @start)  

    END
RETURN
END

---

Try this split function instead and see if you have the same issues.

Convert Delimited value to a List

After you compile it just try:

select * from dbo.fnArray('Does#This#Thing#Really#Work', '#')