AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Tuesday, February 18, 2014

Passing recordset or table to store procedure

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.

Labels: , ,

3 Comments:

Anonymous MDziubek said...

You can also you xml DataType for SQL >= 2005:


CREATE 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

1:59 AM  
Anonymous MDziubek said...

My mistake chande @xDoc.Nodes to @xml.Nodes in proc body...

2:01 AM  
Blogger Alex Dybenko said...

Thanks for follow-up!

10:41 AM  

Post a Comment

<< Home