[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ArrayList as parameter to SQL stored procedure

Posted on 2014-08-25
14
Medium Priority
?
347 Views
Last Modified: 2014-10-07
I actually have to send an XML as a parameter to a SQL stored procedure but I am using SQL 2000 and it does not have XML datatype. The XML is like below

<productlist>
<product>abc3</product>
<product>xyz4</product>
<product>pqr2</product>
</productlist>

abc is the product code and 3 is the quantity. I need to insert product code and quantity in different columns of a table. I think I have two options

(1) Send the XML as varchar(8000) and do the split in the stored procedure.
This seems risky if the XML is more than 8000 characters.

(2) Do the split in my VB.NET application tht calls the stored procedure. Insert product code and quantity in separate arrays and send the arrays to stored procedure as parameters.

Which is the better option? Or is there any other way of doing it? Please advise and let me know how I can do it. Thanks!
0
Comment
Question by:Angel02
  • 5
  • 4
  • 3
  • +1
14 Comments
 
LVL 2

Expert Comment

by:Priya Sudharsan
ID: 40283860
You can go with the 2nd option. You cannot pass an array as a parameter to Stored procedure. Instead, read the XML into a list, read through each element and populate the table directly without calling the stored procedure.

If your stored procedure does any other operations, you can call it once the table is populated.
0
 

Author Comment

by:Angel02
ID: 40283920
Won't it be slow if I insert one row at a time while looping? Is there any other way?
0
 

Author Comment

by:Angel02
ID: 40283958
Also, I have to check if that row already exists before inserting so I might need to have it in a stored procedure.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40284182
You're pretty limited on your options in SQL 2000.  Is this an actual SQL 2000 instance or just a database that is in SQL 2000 compatibility mode?  

If the former, I think you are going to have to stage this data somehow as Priya suggests.  Mainly because you are concerned about it being more than 8000 characters.  If you were sure it would all fit in a varchar(8000) then you could pass it in as a comma-delimited list and then load it into a temp table inside the stored procedure, at which point you could do set operations against it.  If you need to do more with the products than just insert them to a table, load them into a staging table from VB.NET and then call the stored procedure and work with that table.

If its a SQL 2000 database on a 2005+ instance there are some other options we could explore.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40284576
Won't it be slow if I insert one row at a time while looping?
Asbsolutely, Definitely do not do this.

Is there any other way?
There is no reason you cannot pass Xml to a SQL Server 2000 box.  All you have to do is define the parameter in your Stored Procedure as text or ntext.  You can then use OPENXML() to shred the data.

Let me know if you need more help.
0
 
LVL 2

Assisted Solution

by:Priya Sudharsan
Priya Sudharsan earned 200 total points
ID: 40284829
Slowness is based on the number of rows that you are inserting.

There is an other option you can go with:

Create a Datatable in the code and populate the data from XML to Datatable.

Use SqlBulkCopy option to BCP in your data directly to the database table. This will be faster.
0
 
LVL 4

Assisted Solution

by:Randy Knight, MCM
Randy Knight, MCM earned 200 total points
ID: 40285947
As you can see, there are lots of ways to skin this particular cat.  I like the OPENXML idea although it's pretty slow in SQL 2000.  So it may or may not be faster than having to loop to load a staging table.  It really depends on how much data you have.  

However you do it, the bottom line is you need to turn your list of products into a set.  Could be a temp table in your procedure via OPENXML, could be a staging table loaded via VB.NET or SQLBULKCOPY, etc.
0
 

Author Comment

by:Angel02
ID: 40286830
I will try the OPENXML and see if it works.
@Randy Knight
It is a SQL 2000 database on a 2005 instance.
0
 
LVL 4

Expert Comment

by:Randy Knight, MCM
ID: 40286841
In that case what I would do is create a database on the instance to put your sp in (or put in a different database if you have one that makes sense).  Then you can use the xml data type for your parameter and use xquery to shred the xml.  Then just have plain old inserts into the SQL 2000 database.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1600 total points
ID: 40286952
>>I will try the OPENXML and see if it works. <<
It is not difficult at all.  If all you want to do is pass a long list to INSERT/UPDATE a permanent table, than pass it in as a text parameter, Use OPENXML and INSERT/UPDATE to do this.  It is at most 3 lines of code:
1. EXEC sp_xml_preparedocument
2. INSERT/UPDATE with OPENXML clause
3.  EXEC sp_xml_removedocument (whatever you do, do not forget this!)

OPENXML can in some cases be faster than the newer Xml Data Type Methods.
0
 

Author Comment

by:Angel02
ID: 40287172
When I use text or ntext as a parameter, I am getting the following error

The text, ntext, and image data types are invalid for local variables.

Is it not an option for me?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40289400
You can use it as a parameter.  You cannot use it as a local variable.

Let me know if you need an example.
0
 

Author Comment

by:Angel02
ID: 40289508
Yes please. How do I mention it in the stored procedure?
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40291779
You create the Stored Procedure like this:
CREATE PROCEDURE usp_ShredXml
			@MyXml text

AS
DECLARE @iDoc integer
		
SET NOCOUNT ON

EXEC sp_xml_preparedocument @iDoc OUTPUT, @MyXml

SELECT	ProductID
FROM	OPENXML(@iDoc, 'productlist/product', 2) WITH
		(ProductID varchar(20) '.')

EXEC sp_xml_removedocument @iDoc

Open in new window

And you execute it like this:
EXEC usp_ShredXml 
	'<productlist>
		<product>abc3</product>
		<product>xyz4</product>
		<product>pqr2</product>
	</productlist>'

Open in new window

0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question