-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
It uses a while loop with the CHARINDEX function to parse, probably the most
efficient way to do that sort of string parsing in SQL. Is this join against a
parsed string more efficient than sending multiple INSERT statements in a
batch? Maybe...I think the network traffic and code simplification together
more than make up for any sort of inefficiency (if there is any!).
I've attached the body of the function. Hopefully you (and others) will find
it useful!
:)
Joe
Fn_split():
- -----------
CREATE FUNCTION dbo.fn_Split
(
@List varchar(8000),
@Delimiter varchar(20) = ' '
)
RETURNS @retArray TABLE (idx smallint PRIMARY KEY, value varchar(8000))
AS
/*
CREATED BY Joe Cruz
SUMMARY: Borrowed heavily from Burton Roberts article in SQLMag InstantDoc
21071.
This function accept a delimited character list and returns a table with the
index of each delimited value and the value stored in each row of the table.
INPUTS: @List is the delmiited list, up to 8000 characters
@Delimiter is the list delimiter, defaults to a space character
OUTPUTS: @retArray is a TABLE variable with (idx smallint PRIMARY KEY, value
varchar(8000))
as its definition
*/
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint
SET @idx = 0
SET @List = LTrim(RTrim(@List))
SET @iDelimlength = DATALENGTH(@Delimiter)
SET @bcontinue = 1
IF NOT ((@iDelimlength = 0) or (@delimiter = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first
element and
--insert it with its index into the return table.
IF CHARINDEX(@delimiter, @list)>0
BEGIN
SET @value = SUBSTRING(@list,1,
CHARINDEX(@delimiter,@list)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of
the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @list = LTrim(Right(@list,DATALENGTH(@list) -
@iStrike))
END
ELSE
BEGIN
--If you can't find the delimiter in the text, @list is the
last value in
--@retArray.
SET @value = @list
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
- --Increment the index and loop.
IF DATALENGTH(@list)>1
BEGIN
SET @value = SUBSTRING(@list,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @list = SUBSTRING(@list,2,DATALENGTH(@list)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @list)
SET @bcontinue = 0
END
END
END
RETURN
END
> -----Original Message-----
> From: Correa, Orlando (ITSC) [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 12:25 PM
> To: SQL
> Subject: RE: Inserting Multiple Rows in with one INSERT Statement
>
>
> Joe!
>
> This does exactly what I need... Does the fn_split() use a
> while loop to parse the list and populate the table variable
> or another more efficient method? It looks like you pass it
> the list of values and the delimiter to fn_split()... then it
> populates (loops?) and returns a one column (column named
> "value") table variable which you can name in the select
> portion of the INSERT SELECT statement....
>
> Question now is how to efficiently populate the table_varible....
>
> Thanks,
> Orlando
>
> -----Original Message-----
> From: Cruz, Joseph [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, August 23, 2001 8:27 AM
> To: SQL
> Subject: RE: Inserting Multiple Rows in with one INSERT Statement
>
>
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> It looks like a stored procedure is in order. I'm not sure
> how much you've dealt with them in SQL 2000, but one of the
> niftier features in SQL 2000 is User-Defined Functions!
>
> What you might want to attempt to do in your case is pass two
> values into a stored procedure, the first being the uid, and
> then the other being a comma-delimited list of interest ids.
>
> Something like:
>
> <cfquery ... >
> EXEC usp_insert_user_interests @uid=#uid#,
> @interest_list = '1,17,15,26,33,14' </cfquery>
>
> In the stored proc, you could use the fantastically
> functional fn_split() function (check out
> http://www.sqlmag.com/Articles/Index.cfm?ArticleID=21071
> available to subscribers only for now, but maybe you already
> subscribe?) to return a table that you can join your results to:
>
> CREATE PROC usp_insert_user_interests
> (
> @uid integer,
> @interest_list varchar(500)
> )
>
> INSERT user_interest
> SELECT @uid uid, split_table.value
> FROM dbo.fn_split(@interest_list,',') as split_table
>
> RETURN
>
>
> Hope that helps!
>
> :)
>
> Joe
> ============================================
> Joseph Cruz
> Programmer/Analyst
> 3620 Locust Walk, suite 400
> Philadelphia, PA 19104-6302
> 215-898-1220 (work)
> 215-308-0657 (pager)
> 215-768-2071 (cell)
> [EMAIL PROTECTED] ============================================
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 7.0.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO4Zo+NCGp5RuWHIBEQI8CQCgjmdJ/Gz/s4+pD7K3keRRJakAqjkAoP2o
XQ7i+5t97h9MXzZpHWwBHqZl
=uzhX
-----END PGP SIGNATURE-----
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
