We help IT Professionals succeed at work.

ArrayList as parameter to SQL stored procedure

596 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!
Comment
Watch Question

Priya SudharsanProgrammer Analyst

Commented:
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.

Author

Commented:
Won't it be slow if I insert one row at a time while looping? Is there any other way?

Author

Commented:
Also, I have to check if that row already exists before inserting so I might need to have it in a stored procedure.
Randy Knight, MCMPrincipal Consultant

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.
Priya SudharsanProgrammer Analyst
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Randy Knight, MCMPrincipal Consultant
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I will try the OPENXML and see if it works.
@Randy Knight
It is a SQL 2000 database on a 2005 instance.
Randy Knight, MCMPrincipal Consultant

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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?
CERTIFIED EXPERT
Top Expert 2012

Commented:
You can use it as a parameter.  You cannot use it as a local variable.

Let me know if you need an example.

Author

Commented:
Yes please. How do I mention it in the stored procedure?
CERTIFIED EXPERT
Top Expert 2012

Commented:
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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.