I have a series of udf's for SQL 2000 for manipulating lists.
http://www.vawter.com/utilities/
Since udf's didn't exist in SQL 7 they obviously won't work there.
���
~
Don Vawter
----- Original Message -----
From: "Garza, Jeff" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Monday, February 04, 2002 4:01 PM
Subject: RE: Insert a list into a table - help!!!
> Roll this into a stored proceedure and pass your list to that....
>
> Change the insert queries to match your database and rem out the print
> statements...
>
> =============================================================
> BEGIN STORED PROCEEDURE
> =============================================================
>
> CREATE PROCEEDURE usp_InsertList
> @myList nvarchar(255) -- this is the list!!!
> @strIndex int
> @findIndex int
> @theName nvarchar(255)
>
> --rem out the set list once you are passing the variable...
> SET @mylist = 'joe,john,patty,bob'
> SET @strIndex = 1
> SET @findIndex = 1
>
> WHILE (LEN(@myList) <> 0)
> BEGIN
> SET @findIndex = CHARINDEX(',', @mylist, @strIndex)
> PRINT 'Found a Comma at: ' + Convert(nvarchar(10), @findIndex)
> -- Deal with lists that have one member
> IF @findIndex = 0 -- single value list
> BEGIN
> SET @theName = @myList
> PRINT 'This is the Final List Item: ' + @myList
> --INSERT INTO mytable (myfield) VALUES (@theName)
> BREAK
> END
> --otherwise there are still elements...
> ELSE
> BEGIN
> SET @theName = SUBSTRING(@mylist, @strIndex, (@findIndex - @strIndex))
> PRINT 'This is the name we found ' + @theName
> --INSERT INTO mytable (myfield) VALUES (@theName)
> SET @mylist = RIGHT(@mylist, ((LEN(@mylist) - (@findIndex -
> @strIndex))-1))
> PRINT
> '======================================================================='
> END
> END
>
> Jeff Garza
> Lead Developer/Webmaster
> Spectrum Astro, Inc.
> 480.892.8200
> [EMAIL PROTECTED]
> http://www.spectrumastro.com
>
>
>
> -----Original Message-----
> From: Thanh Nguyen [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 04, 2002 2:38 PM
> To: CF-Talk
> Subject: RE: Insert a list into a table - help!!!
>
>
> MSSQL
>
> -----Original Message-----
> From: Garza, Jeff [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 04, 2002 12:24 PM
> To: CF-Talk
> Subject: RE: Insert a list into a table - help!!!
>
>
> What database are you using?
>
> Jeff Garza
> Lead Developer/Webmaster
> Spectrum Astro, Inc.
> 480.892.8200
> [EMAIL PROTECTED]
> http://www.spectrumastro.com
>
>
>
> -----Original Message-----
> From: Thanh Nguyen [mailto:[EMAIL PROTECTED]]
> Sent: Monday, February 04, 2002 1:20 PM
> To: CF-Talk
> Subject: Insert a list into a table - help!!!
>
>
> I have a list with at least 3 items. And I want to insert it into
> table.
> right now, I'm looping through the list to insert them into db, but
> this is
> like writing multiple cfquery's. and what happens if my list has 20
> items.
>
> <cfloop index="index" list="#mylist#>
> <cfquery >
> insert into my_table
> (id, itemName)
> values
> (#ID#, '#index#' )
> </cfquery>
> </cfloop>
>
> Is there a better way to do this?
>
>
>
>
>
>
>
______________________________________________________________________
Why Share?
Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER
Instant Activation � $99/Month � Free Setup
http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
