如何通过列表存储过程 [英] How to pass a list to store procedure

问题描述

亲爱的，

我现在正在尝试将列表传递到存储过程中.这是示例代码.

-获取数据列表

Dear there,

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

-- Get a list of data

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

输出= item1，item2，item3

-很可能我想像这样传递这个数组吗?

Output = item1,item2,item3

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

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

预先感谢，
Ying Lamp

Thanks in advance,
Ying Lamp

推荐答案

嗨Ying
您应该使用xml进行查询，将xml参数传递给SP以插入到一个或多个表中.例如:

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:

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:

/* 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

这篇关于如何通过列表存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！