SQL server 2008 - Insert into table from SP, using two input parameter list

Question

I've to modify a Stored Procedure which inserts the data passed as input parameter in CSV varchar(MAX) format, but now i've to pass two lists to SP and have to insert it into the table

data which I pass is as following

lstid = 1,2,3,4,5 etc.
lstvalue = 10,20,22,35,60 etc.

here lstid maps to lstvalue, means lstid = 1's value will be 10, lstid = 2's value will be 20 and so on

what should I do to insert the record based on mapping

I am using a function to seprate the CSV value and than stores it in temptable, but it work for obly one column

function is same as here

http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=14185

See the article here for various options: http://www.sommarskog.se/arrays-in-sql-2005.html — David Brabant, May 09 '12 at 12:55

score 1 · Accepted Answer · edited May 23 '17 at 11:56

If you are forced to do this in a stored procedure and your arrays are equal size you can join the two lists, split them, and then join on position (the number of elements in each array) to get the linked set you need.

The below example uses a number table, but you can replace that split operation with any.

-- if you dont have a number table:
/*
   create table [dbo].[Number](n int not null primary key clustered);
   insert into dbo.Number
        values  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
                (11),(12),(13),(14),(15),(16),(17),(18),(19),(20)

*/

declare @lstid varchar(100) = '1,2,3,4,51',
        @lstvalue varchar(100) = '10,20,22,35,60'


declare @Length tinyint,
        @Input  varchar(8000),
        @Delimiter  char(1)

-- sanity check
if len(@lstid)-len(replace(@lstid, ',', '')) <> len(@lstvalue)-len(replace(@lstvalue, ',', ''))
begin
    raiserror('lists are not equal', 16, 1);
    return;
end

--count the numbers of elements in one of the arrays
select @Length = len(@lstid)-len(replace(@lstid, ',', ''))+1;

--join the two arrays into one
select @Input = @lstid + ',' + @lstvalue;

set @Delimiter = ',';

;with cte (i,s)
as  (   
        select  row_number() over (order by n.n asc) [i],
                substring(@Delimiter + @Input + @Delimiter, n.n + 1, charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n + 1) - n.n - 1) [s]
        from    dbo.Number n
        where   n.n = charindex(@Delimiter, @Delimiter + @Input + @Delimiter, n.n) and
                n.n <= len(@Delimiter + @Input)
    )
select  a.s, b.s
from    cte a
join    cte b on
        a.i+@Length = b.i
order
by      a.i;

return

score 0 · Answer 2 · edited May 23 '17 at 11:56

0

Create a data table with the data in .net code and pass that to SP.

How to pass data table to Sp from .net

edited May 23 '17 at 11:56

Community

1
1

answered May 09 '12 at 12:34

Romil Kumar Jain

20,239
9
63
92

score 0 · Answer 3 · answered May 09 '12 at 12:39

You can pass your parameter lists as XML.

The parameter:

<lst><id>1</id><value>10</value></lst>
<lst><id>2</id><value>20</value></lst>
<lst><id>3</id><value>22</value></lst>
<lst><id>4</id><value>35</value></lst>
<lst><id>5</id><value>60</value></lst>

and the procedure

create procedure AddXML
  @XML xml
as
insert into YourTable(id, value)
select N.value('id[1]', 'int'),
       N.value('value[1]', 'int')
from @XML.nodes('/lst') as T(N)

SQL server 2008 - Insert into table from SP, using two input parameter list

3 Answers3