Passing an array parameters and insert into temporary table

Hi,

I'd like to pass to a StoredProc comma-separated string and insert the data into a temporary table for futher treatment. Exemple:

string will be like:
'abc123', 100, True, '001-123-01A', 99, 'abc456', 110, True, '001-888-02B', 90, 'abc789', etc...

temporary table will be:
create table #Details (
  Product varchar(50),
  Quantity int,
  NewProduct bit,
  Localisation varchar(50),
  userId int
)

the 5 first entries will be inserted into the table, then the 5 following entries, and so on until the end of the string. how can I do that??

thanks for your time and help
Dominic34Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Shaun KlineLead Software EngineerCommented:
If you are able to change the comma that separates the row data to a semi-colon, you can use code similar to the attached.
DECLARE @Data nvarchar(max)
DECLARE @Xml XML

SET @Data = '1, a, 01/01/2010;2, b, 02/02/2010'
SELECT @Xml = cast('<row><col>' + REPLACE(REPLACE(@Data, ';', '</col></row><row><col>'), ', ', '</col><col>') + '</col></row>' AS XML)

SELECT x.value('./col[1]', 'int') as id, x.value('./col[2]', 'varchar(1)') as textvalue,
    x.value('./col[3]', 'datetime') as thedate
FROM @xml.nodes('//row') as records(x)

Open in new window

0
Dominic34Author Commented:
thanks Shaun

i'm not really familiar with XML, can you give me an example where the string is inserted into a table ?
0
dan_masonCommented:
My solution just involves you having to set up an additional function to detect the "Nth" time a character occurs (in your case, comma).

Doesn't account for examples where you don't have proper multiples of 5 in your input string, but you could build that in fairly easily.

 
/* 
Function to return the Nth Occurrence of a string within a string
	EXAMPLE : SELECT dbo.NthOccurrence('a', 'abbabba', 3)
	returns the CHARINDEX of the third occurrence of 'a', which is 7
*/
CREATE FUNCTION [dbo].[NthOccurrence] (@SearchFor varchar(max), @SearchWithin varchar(max), @Occurrence smallint)
RETURNS smallint
AS
BEGIN
	DECLARE @pos int, @counter int, @ret int

	SET @pos = CHARINDEX(@SearchFor, @SearchWithin)
	SET @counter = 1

	IF @Occurrence = 1 SET @ret = @pos
	ELSE
		BEGIN
			WHILE (@counter < @Occurrence)
			BEGIN
				SET @ret = CHARINDEX(@SearchFor, @SearchWithin, @pos + 1)
				SET @counter = @counter + 1
				SET @pos = @ret
			END
		END	
	RETURN(@ret)
END
GO

/* Script to split up a CSV string into batches of 5 and insert them into a temporary table */


create table #Details (
  Product varchar(50),
  Quantity int,
  NewProduct bit,
  Localisation varchar(50),
  userId int
) 

/* Variables - note that the string input does not have quotes round any of the values */
DECLARE @commaIndex smallint, @substring varchar(140), @newRow varchar(140), @SQL varchar(500)
DECLARE @string varchar(4000)
SELECT @string='abc123, 100, True, 001-123-01A, 99, abc456, 110, True, 001-888-02B, 90, abc789, 120, True, 001-999-03C,90'
SELECT @string=REPLACE(@string,' ','')+',' -- remove extra spaces and add a comma on the end, so we can detect n*5 complete 'columns'


WHILE LEN(@string)>0
BEGIN
	SELECT @commaIndex=dbo.NthOccurrence(',',@string,5) -- where does the 5th comma occur in the string
	SELECT @substring=SUBSTRING(@string,1,@commaIndex) -- these are the first 5 values; storing this separately so we can strip it out later in the loop
	SELECT @newRow=''''+ REPLACE(LEFT(@substring,LEN(@substring)-1),',',''',''')+'''' -- turn the values into a valid string for INSERT

	SELECT @SQL='
		INSERT #Details (Product,Quantity,NewProduct,Localisation,userID)
		VALUES ('+@newRow+')'
	EXEC (@SQL) -- run the INSERT statement
	
	SET @string=REPLACE(@string,@substring,'') -- strip out the substring we just processed, so we can loop back for the next substring
END

Open in new window

0
Shaun KlineLead Software EngineerCommented:
Here's the example using your string (with the semi-colon) and with the temp table insert:
DECLARE @Data nvarchar(max)
DECLARE @Xml XML

create table #Details (
  Product varchar(50),
  Quantity int,
  NewProduct bit,
  Localisation varchar(50),
  userId int
) 

SET @Data = 'abc123, 100, True, 001-123-01A, 99;abc456, 110, True, 001-888-02B, 90'
SELECT @Xml = cast('<row><col>' + REPLACE(REPLACE(@Data, ';', '</col></row><row><col>'), ', ', '</col><col>') + '</col></row>' AS XML)

insert into #Details
SELECT x.value('./col[1]', 'varchar(50)') as Product, x.value('./col[2]', 'int') as Quantity,
    case x.value('./col[3]', 'varchar(5)') when 'True' then CAST(1 as bit) else cast(0 as bit) end as NewProduct,
    x.value('./col[4]', 'varchar(50)') as Localisation, x.value('./col[5]', 'int') as userId
FROM @xml.nodes('//row') as records(x)

select * from #Details

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dominic34Author Commented:
works like a charm!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.