Checking if the input string is matching with the enum value

Viewed 24
ALTER FUNCTION[dbo].[EnumValidate1]
(
@P_Input NVARCHAR(55)
)
RETURNS
@returnList TABLE ([@V_RESULT] [nvarchar] (500))
AS
BEGIN
DECLARE @V_EnumValue NVARCHAR(255)
DECLARE @V_pos INT
DECLARE @V_RESULT NVARCHAR(255)
SET @V_EnumValue = 'A,D,T,Y,Z';
WHILE CHARINDEX(',', @V_EnumValue) <0 AND CHARINDEX(',', @V_EnumValue)< LEN(@V_EnumValue)
BEGIN
  SELECT @V_pos  = CHARINDEX(',', @V_EnumValue)  
  SET @V_EnumValue = SUBSTRING(@V_EnumValue, 1, @V_pos-1)
END
INSERT INTO @returnList
SELECT @V_RESULT
IF @P_Input != @V_EnumValue
BEGIN
  SET @V_RESULT ='NOT MATCHED'
  INSERT INTO @returnList
  SELECT @V_RESULT
  RETURN
END
SET @V_RESULT= 'MATCHED'
INSERT INTO @returnList
SELECT @V_RESULT
RETURN
END

example: select[dbo].EnumValidate1

It shall return matched

example2:select[dbo].EnumValidate1

it shall return unmatched

0 Answers
Related