Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Calling UDF - Getting An Error
Message
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Calling UDF - Getting An Error
Miscellaneous
Thread ID:
01326427
Message ID:
01326427
Views:
52
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

	-- 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
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
Next
Reply
Map
View

Click here to load this message in the networking platform