Alex & Access

February 18, 2014

Passing recordset or table to store procedure

Filed under: Access, SQL Server — access @ 2:09 pm

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.

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: