CREATE FUNCTION udfCommaListToTable (@cList VARCHAR(MAX)) RETURNS @IntKeyTable TABLE (IntKey INT, Counter INT PRIMARY KEY CLUSTERED ([IntKey])) AS BEGIN -- Define variables DECLARE @nPosition INT DECLARE @cTempValue VARCHAR(MAX) DECLARE @nIntKey INT DECLARE @nCounter INT -- Set variable default values SET @nCounter = 0 SET @cList = RTRIM(@cList) -- If the parameter string does not end in a comma, then append -- a comma to the end of the string IF RIGHT(@cList, 1) <> ',' SET @cList = @cList + ',' -- Loop while there are commas in the parameter string WHILE PATINDEX('%,%', @cList) <> 0 BEGIN -- Increment the counter SET @nCounter = @nCounter + 1 -- Get the position of the first comma SELECT @nPosition = PATINDEX('%,%', @cList) -- Extract the value from the list SELECT @cTempValue = LEFT(@cList, @nPosition - 1) -- Convert the value to an integer SET @nIntKey = CAST(@cTempValue AS INT) -- Insert the value into the table INSERT INTO @IntKeyTable VALUES (@nIntKey, @nCounter) -- Remove the comma from the string SELECT @cList = STUFF(@cList, 1, @nPosition, '') END RETURN ENDI am trying to test it:
DECLARE @cList VARCHAR(10) SET @cList = '1,2,3' SELECT udfCommaListToTable(@cList) AS TListI get the error "'udfCommaListToTable' is not a recognized built-in function name."