Passing a Table to A Stored Procedure

Question

Passing a Table to A Stored Procedure

Viewing 15 posts - 76 through 90 (of 100 total)

You must be logged in to reply to this topic. Login to reply

anshul2181981 SSC Enthusiast Points: 105 More actions · Answer 1

Hi Jacob,

I was really excited after reading your article and started to try implementing the xml stuff on my sql server 2000 query analyzer.

I could select the table data as xml using the select statements...but when i try to declare a variable of type xml it gave me an error. Here is a code snippet of that:

DECLARE @x XML

This is the error message that i got:

Column or parameter #-1: Cannot find data type XML.

Parameter '@x' has an invalid data type.

I have looked on many forums to solve this issue, but cannot. Can you throw some light on this?

the sqlist SSCrazy Points: 2398 More actions · Answer 2

SQL server 2000 doesn't support the XML data type. It was introduced with SQL server 2005.

Don't just give the hungry man a fish, teach him how to catch it as well.

the sqlist

jacob sebastian SSChampion Points: 11822 More actions · Answer 3

SQL Server 2000 does not support the XML data type. If you want to work with parameters in XML format, you can still pass them as TEXT and use OPENXML() to query on them.

Have a look at

http://www.sqlservercentral.com/articles/Miscellaneous/2908/

http://www.sqlservercentral.com/articles/Miscellaneous/2909/

http://www.sqlservercentral.com/articles/Miscellaneous/2911/

.

anshul2181981 SSC Enthusiast Points: 105 More actions · Answer 4

the statement :

declare @x xml

is not working for me in sql server 2005 too..Is there any configuration I need to do specifically to enable that.

jacob sebastian SSChampion Points: 11822 More actions · Answer 5

I am not aware of any such setting. XML is a built-in data type like INT or VARCHAR. So there should not be a special setting to enable it.

try SELECT @@version and see what you are getting.

.

Love2Learn SSC Rookie Points: 28 More actions · Answer 6

Jacob,

Tell me.. how to take the reference of last record updated in my destiny, an Oracle Datbase Table and copy the next record from my sql table???

please

Vasu

jacob sebastian SSChampion Points: 11822 More actions · Answer 7

Vasudev Tantry (12/18/2007)
Jacob,
Tell me.. how to take the reference of last record updated in my destiny, an Oracle Datbase Table and copy the next record from my sql table???
please
Vasu

.

Argneka SSC Enthusiast Points: 104 More actions · Answer 8

Hi,

Hold the Bus. I think I figured it out. I will sed you an update when I am done to see if there is a better way to accomplish this.

Thanks,

Alan

VG-619426 Hall of Fame Points: 3914 More actions · Answer 9

SQL Server 2008 has better solution. Now we can declare table valued parameter.

http://technet.microsoft.com/en-us/library/bb510489(SQL.100).aspx

Argneka SSC Enthusiast Points: 104 More actions · Answer 10

Hi,

Yes I am aware of this. Unfortunatly I am using SQL2K and SQL2005. My soultion does work well for it's intended use. Not Ideal for huge amounts of data.

Thanks...

sachinthamke SSC Enthusiast Points: 152 More actions · Answer 11

Nice Article with clear explanation

I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML

as per my knowledge xml data has limit of 8000 char.

How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

Sachin Thamke
Mumbai, India

sachinthamke SSC Enthusiast Points: 152 More actions · Answer 12

Nice Article with clear explanation

I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML

as per my knowledge xml data has limit of 8000 char.

How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

Sachin Thamke
Mumbai, India

FreeHansje SSChampion Points: 11751 More actions · Answer 13

Have a look here: very interesting article with several ways how to pass arrays/tables to SP. Ofcourse, you don't REALLY pass a table, but his solutions and examples have helped me to solve a similar problem.

Greetz,
Hans Brouwer

jacob sebastian SSChampion Points: 11822 More actions · Answer 14

sachinthamke (6/19/2008)
Nice Article with clear explanation
I have one query, whenever inserting records in the details table (OrderDetails), is there any limit for XML
as per my knowledge xml data has limit of 8000 char.
How to handles if my order items are 10,000 and the XML file is not satisified the lenght.

The XML data type can store upto 2 Gb of data.

.

jose-276033 SSC Veteran Points: 219 More actions · Answer 15

Hello,

not sure if I have the full picture! Why would you like to pass a full table to a procedure, why not pass a tablename instead and open the table in the store procedure or function?

Thanks

Jose