SQL Server Performance Forum – Threads Archive

How to pass text or ntext as a parameter

I was playing with some code found here: http://www.sommarskog.se/arrays-in-sql.html Particularly this function: CREATE FUNCTION duo_text_split_me(@list ntext,
@delim nchar(1) = N’,’)
RETURNS @t TABLE (str varchar(4000),
nstr nvarchar(2000)) AS
BEGIN
DECLARE @slices TABLE (slice nvarchar(4000) NOT NULL)
DECLARE @slice nvarchar(4000),
@textpos int,
@maxlen int,
@stoppos int SELECT @textpos = 1, @maxlen = 4000 – 2
WHILE datalength(@list) / 2 – (@textpos – 1) >= @maxlen
BEGIN
SELECT @slice = substring(@list, @textpos, @maxlen)
SELECT @stoppos = @maxlen – charindex(@delim, reverse(@slice))
INSERT @slices (slice) VALUES (@delim + left(@slice, @stoppos) + @delim)
SELECT @textpos = @textpos – 1 + @stoppos + 2 — On the other side of the comma.
END
INSERT @slices (slice)
VALUES (@delim + substring(@list, @textpos, @maxlen) + @delim) INSERT @t (str, nstr)
SELECT str, str
FROM (SELECT str = ltrim(rtrim(substring(s.slice, N.Number + 1,
charindex(@delim, s.slice, N.Number + 1) – N.Number – 1)))
FROM Numbers N
JOIN @slices s ON N.Number <= len(s.slice) – 1
AND substring(s.slice, N.Number, 1) = @delim) AS x RETURN
END
My question is, since you cannot define text or ntext as a local variable, how can you call this function? Can it be called from a stored procedure?

Referhttp://www.sqlxml.org/faqs.aspx?faq=61 link HTH Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

That’s how I started doing it, but I was hoping it would only be temporary… I guess there’s no other way.

May be it is best to drop an email to author (Sommarskog) for relevant information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>