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.";