在存储过程参数中发送许多值 [英] Send Many Values in a Store Procedure Parameter

问题描述

大家好，

请在存储过程的数组参数(varchar，整数)中发送许多值，
因为我不想插入很多，这是主要原因.
请告诉我这是否可行???

怎么样???

提前谢谢.

Leo

Hello every one,

Please I need to send many values in a array-parameter (varchar, integer) of stored procedure,
because i do not want to make many inserts, this is the principal reason.

Pleae tell me if it is posible???

How???

Thanks in advance.

Leo

推荐答案

Google [ ^ ]是您的朋友:)
这些解决方案中的大多数涉及逗号分隔的值，但其中1种具有XML解决方案，例如

Google[^] is your friend :)
Most of those solutions involve comma seperated values but 1 has XML solution such as

http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm [^ ]

CREATE PROC dbo.GetOrderList4
(
    @OrderList varchar(1000)
)
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @DocHandle int
    EXEC sp_xml_preparedocument @DocHandle OUTPUT, @OrderList
    SELECT o.OrderID, CustomerID, EmployeeID, OrderDate
    FROM    dbo.Orders AS o
        JOIN
        OPENXML (@DocHandle, '/ROOT/Ord',1) WITH (OrderID  int) AS x
        ON o.OrderID = x.OrderID
    EXEC sp_xml_removedocument @DocHandle
END
GO
GRANT EXEC ON dbo.GetOrderList4 TO WebUser
GO

--Call this stored procedure as shown below, and it will retrieve OrderID, CustomerID, EmployeeID and OrderDate columns for the given order numbers:
EXEC dbo.GetOrderList4 '
<ROOT>
<Ord OrderID = "10248"/> <Ord OrderID = "10252"/>
<Ord OrderID = "10256"/> <Ord OrderID = "10261"/>
<Ord OrderID = "10262"/> <Ord OrderID = "10263"/>
<Ord OrderID = "10264"/> <Ord OrderID = "10265"/>
<Ord OrderID = "10300"/> <Ord OrderID = "10311"/>
<Ord OrderID = "11068"/> <Ord OrderID = "11069"/>
<Ord OrderID = "11070"/> <Ord OrderID = "11071"/>
<Ord OrderID = "11072"/> <Ord OrderID = "11073"/>
<Ord OrderID = "11074"/> <Ord OrderID = "11075"/>
<Ord OrderID = "11076"/> <Ord OrderID = "11077"/>
</ROOT>'
GO

这篇关于在存储过程参数中发送许多值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！