How to pass a list to store procedure

Question

0.00/5 (No votes)

See more:

Dear there,

I am now trying to pass a list into store procedure. Here is sample coding.

-- Get a list of data

SQL

Select ItemNumber As ItemArray
FROM dbo.Items 
WHERE ID = '1'

Output = item1,item2,item3

-- Izit possible I want to pass this array like this ?

SQL

INSERT INTO dbo.ItemDetails (ID,ItemNumber)
VALUES ('2',ItemArray)

Thanks in advance,
Ying Lamp

Posted 28-Nov-11 17:43pm

Silent Radar

Updated 28-Nov-11 17:44pm

RaisKazi

v2

Add a Solution

Comments

RaisKazi 28-Nov-11 23:44pm

Edited: 1) Formatting 2) Added "pre" tag.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

omid.nazifi · Accepted Answer · 2011-11-28T18:00:00

Hi Ying
You should use a xml into query, you pass a xml parameter to SP for insert into a table( or tables). For example:

SQL

CREATE PROCEDURE [usp_Customer_INS_By_XML]
@Customer_XML XML
AS
BEGIN
EXEC sp_xml_preparedocument @xmldoc OUTPUT, @Customer_XML

--OPEN XML example of inserting multiple customers into a Table.
INSERT INTO CUSTOMER
(
First_Name
Middle_Name
Last_Name
)
SELECT
First_Name
,Middle_Name
,Last_Name
FROM OPENXML (@xmldoc, '/ArrayOfCustomers[1]/Customer',2)
WITH(
 First_Name VARCHAR(50)
,Middle_Name VARCHR(50)
,Last_Name VARCHAR(50)
)

EXEC sp_xml_removedocument @xmldoc
END

Here's another example:

SQL

/* Create the stored procedure */
create procedure ParseXML (@InputXML xml)
as
begin
    declare @MyTable table (
        id int,
        value int
    )

    insert into @MyTable 
        (id, value)
        select Row.id.value('@id','int'), Row.id.value('@value','int') 
            from @InputXML.nodes('/Rows/Row') as Row(id)        

    select id, value
        from @MyTable
end
go

/* Create the XML Parameter */
declare @XMLParam xml
set @XMLParam = '<rows>
                     <row id="1" value="100" />
                     <row id="2" value="200" />
                     <row id="3" value="300" />
                 </rows>'

/* Call the stored procedure with the XML Parameter */
exec ParseXML @InputXML = @XMLParam

/* Clean up - Drop the procedure */
drop procedure ParseXML
go