These days you can pass a table variable to SQL Server stored procedure, it can be easy done in .NET, but not sure you can do this in Access with VBA. But we can do the same using XML. Imagine you need to pass Order details of several rows. Create a string variable with following XML:
<Table><Row ProductID="1" Qty ="2" Price="3"></Row><Row ProductID ="3" Qty ="4" Price="5"></Row></Table>
Make a store procedure like:
CREATE PROCEDURE [dbo].[spListOrder]
@OrderID int,
@XML as varchar(max)
AS
begin
declare @docHandle int
--load DOM
EXEC sp_xml_preparedocument @docHandle OUTPUT, @XML Select * from (Select ProductID, Qty, Price FROM OPENXML(@docHandle, N'/Table/Row') WITH (ProductID int, Qty float, Price money)
END
And run it as:
ExecureSQL "spListOrder " & lngOrder & "," & strXML
That is! Note, you you run once sp_xml_preparedocument – and you can use (Select ProductID, Qty, Price FROM OPENXML(@docHandle, N'/Table/Row') WITH (ProductID int, Qty float, Price money) as any other select statement, in joins, updates, inserts, etc.
You can also you xml DataType for SQL >= 2005:
ReplyDeleteCREATE PROCEDURE [dbo].[spListOrder] @OrderID int, @XML xml
AS
begin
select
t.value('@ProductID','integer') as [ProductID]
,t.value('@Qty','float') as [Qty]
,t.value('@Price','money') as [Qty]
from
@xDoc.nodes('/Table/Row') as a(t)
end
My mistake chande @xDoc.Nodes to @xml.Nodes in proc body...
ReplyDeleteThanks for follow-up!
ReplyDelete