Invalid length parameter passed to the LEFT or SUBSTRING function - This error while running function

Viewed 31
CREATE FUNCTION[dbo].[EnumValidate1]
(
@P_TeiValue NVARCHAR(5),
@P_Input NVARCHAR(55)
)
RETURNS
 @returnList TABLE ([ERROR] [nvarchar] (500))
AS
BEGIN

 DECLARE @EnumValue NVARCHAR(255)
 DECLARE @pos INT
 DECLARE @RESULT NVARCHAR(255)
 SET @EnumValue = (select Enumeration_Values from fh.SPEC_XSD_TEI where tei=@P_TeiValue);
 WHILE CHARINDEX(',', @EnumValue) > 0
 BEGIN
  SELECT @pos  = CHARINDEX(',', @EnumValue)  
  SELECT @EnumValue = SUBSTRING(@EnumValue, 1, @pos-1)

  INSERT INTO @returnList 
  SELECT @EnumValue

  SELECT @EnumValue = SUBSTRING(@EnumValue, @pos+1, LEN(@EnumValue)-@pos)
  
 END
 INSERT INTO @returnList
 SELECT @EnumValue
 IF @P_Input != @EnumValue
 BEGIN
  SET @RESULT ='NOT MATCHED'
  INSERT INTO @returnList
 SELECT @RESULT
  RETURN 
 END
 SET @RESULT= 'MATCHED'
 INSERT INTO @returnList
 SELECT @RESULT
 RETURN
END

When I try calling this function with SELECT * FROM [dbo].[EnumValidate1]('CHG','a') I get error:

Msg 537, Level 16, State 3, Line 42
Invalid length parameter passed to the LEFT or SUBSTRING function.";

0 Answers
Related