Passing a recordset to a stored procedure

[AppDev76]

Hi,
I need to pass a recordset to a stored Procedure.
Basically I'm retriving a set or records from a third-party application using API calls. I stored the returned set in a datatable. Now what I need to do is to pass these values to a stored Procedure , do some processing and then return the results back.
One way to do this is to pass the datatable as an XML document to SQL Server SP and have the SP create the table from the XML doc. The problem with this is that the XML I'm getting from the third-party app is kinda tricky and not very clean. Its element centric and their naming conventions is wierd.

What other options do I have to pass a set of records to SQL Server using ADO.Net and VB.

Thanks

 

[NoCoolHandle]

Is it possible to create a table type input parameter?

I havn't tried it, but it should work.

Another option might be to create a datatable on the .net client and map it's sturcture to the destination table, populate it, then apend it to the table via a dataadapter..

You might find that the process of sending a full recordset isn't that much faster than looping and single values procs.. (the overhead is greater, even though the data is passed effeciently (binary data) - or atleast it was in ADO)

Worth looking at.



Rob

 

[AppDev76]

NoCoolHandle,
thanks for the quick reply.
1. As far as I know you can use a table type variable in a SP but you can't pass it as a paramter.
2. I am really new to ADO.Net and therefore don't completly understand what you mean by mapping the datatable to table on SQL Server. I need to pass the data from the .Net client. Can you please send me a sample code or resource on the web for this?
3.I am actually thinking about looping through the datatable. The only problem is that for each record I have to make a trip to the SQL Server and run the stored procedure and return a set of variables. How ever if I send the batch my operation on the server will be set based and more efficint. The result is also going to be a recordset. Ofcource the difference won't be much if you are talking about a few records, however if you are doing the same thing many times per day for a large number of records the difference will be evident.

Thanks

 

[NoCoolHandle]

. As far as I know you can use a table type variable in a SP but you can't pass it as a paramter.

Well that is inconvenient and shortsighted ofthe DotNet develpoment team!

What about a sqldbtype.variant?


Re 2. I will see what I can find. I should have something somewhere..

Re 3. I did a bunch of tests in the old days of ado.
it is hard to say just how much one option is over
another in dot net without doing some serious
testing, however what I remember of the results..

When submitting a recordset for a bulkupdate the time spent sending the information over the network was smaller than any other option, however the SQLSystem had to work quite hard processing the "cursor" updatestatement..

When submitting all the records via a storedproc 1 record at a time, the network time went up, but the time sqltook to process the update was less. I would tend to think this is because you are sending the insert via a recordset modification.

I am wondering about how realistic another idea I have is. Have you thought about perfroming this as a multistep action.

ie Step 1 Use the t-sql "Bulk Copy" statement to import the data to a temp table and
step 2 do an Insert into from select * from TEMPTABLE

Bulk insert is reaonably simple and insanely fast. Not as direct, but you might find it works well.


Just a thought..

Rob

 

[AppDev76]

Thanks alot.
I appreciate your help