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
