CODEWITHSUNDEEP
sql-server-2008stored-procedures
IT ppl
IT ppl

Reputation: 2647

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

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

Upvotes: 1

Views: 1471

Answers (3)

nathan_jr
nathan_jr

Reputation: 9282

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

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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)

Upvotes: 0

Romil Kumar Jain
Romil Kumar Jain

Reputation: 20745

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

How to pass data table to Sp from .net

Upvotes: 0

Related Questions