I wrote this UDF which takes a comma delimited list and converts it to a table of PK's:
CREATE FUNCTION udfCommaListToTable (@cList VARCHAR(MAX))
RETURNS @IntKeyTable TABLE
(IntKey INT, Counter INT PRIMARY KEY CLUSTERED ([IntKey]))
AS
BEGIN
DECLARE @nPosition INT
DECLARE @cTempValue VARCHAR(MAX)
DECLARE @nIntKey INT
DECLARE @nCounter INT
SET @nCounter = 0
SET @cList = RTRIM(@cList)
IF RIGHT(@cList, 1) <> ','
SET @cList = @cList + ','
WHILE PATINDEX('%,%', @cList) <> 0
BEGIN
SET @nCounter = @nCounter + 1
SELECT @nPosition = PATINDEX('%,%', @cList)
SELECT @cTempValue = LEFT(@cList, @nPosition - 1)
SET @nIntKey = CAST(@cTempValue AS INT)
INSERT INTO @IntKeyTable
VALUES (@nIntKey, @nCounter)
SELECT @cList = STUFF(@cList, 1, @nPosition, '')
END
RETURN
END
I am trying to test it:
DECLARE @cList VARCHAR(10)
SET @cList = '1,2,3'
SELECT udfCommaListToTable(@cList) AS TList
I get the error "'udfCommaListToTable' is not a recognized built-in function name."
What am I doing wrong?
Everything makes sense in someone's mind
public class SystemCrasher :ICrashable
In addition, an integer field is not for irrational people