SQL Stored Procedure Insert List of variables

[dmj07]

Hi,

I am running a stored procedure where I pass in a large list of variables e.g.

ALTER PROCEDURE dbo.Insert(@Variable1 varchar,
@Variable2 varchar,.....etc.

What I would like to know, is there a possible way to loop around all the variables, I think there is 16 of them, and insert them into a table with a new row for each variable in the list??

Many thanks in advance.

[ck9663]

What you could do is to concatenate all the variables into a single variable separated by a comma (or a bar).

Then use this technique to convert it into a result set. Then you can do a single INSERT command.

Happy coding!

-- CK

[dmj07]

I changed my way of doing this and now have a varchar variable with the three values that I want to insert into the table all separated by a comma e.g.

DECLARE @Row1 varchar(10)

SET @Row1 = (SELECT Col1 + ',' + Col2 + ',' + Col3 FROM Table)

IF NOT(@Row1 = NULL)

BEGIN
INSERT INTO Table2
(ID, Col1, Col2, Col3)
VALUES (@ID, *********sticking point*********

END

As you can see from the code I want to split the @Row1 variable to separate them into three different inserts.

Many thanks.

[ck9663]

Are you planning to do something like this:

INSERT INTO Table2
                     (ID, Col1)
         VALUES (@ID, @FirstColumnInRow1)
 
INSERT INTO Table2
                      (ID, Col2)
          VALUES (@ID, @SecondColumnInRow1)
 
INSERT INTO Table2
                      (ID, Col3)
          VALUES (@ID, @ThirdColumnInRow1)
 

That would mean you'll have rows with at least one empty CO1, COL2 and COL3.

Is that what you're trying to do?


-- CK

[dmj07]

Yes thats pretty much it

[ck9663]

The link I gave you will help you convert your string into a some sort of table that you can use to do a INSERT INTO...SELECT

-- CK

[dmj07]

Thanks for your help I got it working with the link you provided