Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
 Posted - 2004-08-15 : 14:02:40
|
| Here's a question for ya:I know TSQL does not support arrays and I researched around and found a work-around to this, but putting values in a delimenated form, and so forth.Setup: Tables: Client - Holds client info Invoice - Holds client invoices for purchases made InvoiceDeatil - Hold items in a particular invoice for a purchase made Sproc: proc_AddInvoice - Adds invoice info, along with the invoice detailsSo I don't make many hits to the database, I send the items in a comma deliminated form. ie: [itemid for product 1], [quantity for product 1], [dicount percent for particular product 1],[itemid for product 2], [quantity for product 2], [dicount percent for particular product 2]The thing is this:If I have a client who makes an order online, I use ADO to call a sproc and send parmeters to the sproc, and the sproc adds a record to the Invoice table, then by parsing a comma deliminated value in the sproc, it adds the items bought to the InvoiceDetail table. The variable receiving the items is a varchar(8000), but what if the actualy value passed exceeds 8000? I thought of spliting it into 2 params of 8000, allowing for 16000 characters, but then I thought what if it passes 16000?Realistically speaking, I'm 99.9999999999999999999999% sure it will NEVER exceed 8000, buy I decided to post this question in an effort to find if someone has got a 'universal' solution for this. I had one before, where I call a sproc to add the items individually form the app to the db, but I remember getting some kind of error about a connection is already open or something (was a long time ago) and that's where I read about doing the whole process in one sproc.Thanks to all whole read this, and I appreciate any ideas anyone shares.- RoLY roLLs |
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 15:15:50
|
| I've seen some SPLITTER functions around that will split from a TEXT, rather than VARCHAR - but I don't have a URL to hand I'm afraid.Kristen |
 |
|
|
RoLYroLLs
Constraint Violating Yak Guru
255 Posts |
Posted - 2004-08-15 : 15:34:06
|
| hmmm. i didn't think about passing as a text. What are ADO's limit to passing data as TEXT (I believe it to be 'adLongVarChar')My worry is not splitting the data at the database, my worry is the amount of data I can pass, instead of passing it in two sections or three (if it exceeds 16,000), I'd rather pass it as one long text.- RoLY roLLs |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-16 : 01:32:34
|
| We have ADO stuff that deals with Content Management that passes TEXT. We thought we'd have to do Chunking and all that palava, but in practice it hasn't been necessary - users regularly store bits of text that are around 64K, so I expect that you can pump "plenty" through that route.Kristen |
|
|
|
Andraax
Aged Yak Warrior
790 Posts |
Posted - 2004-08-16 : 02:20:55
|
| I've passed a couple of megabytes into text using ADO, and that worked OK. You should be able to use the full scope (up to 2 GB) if necessary. However, don't know if the performance is good. |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
|
|
ehorn
Master Smack Fu Yak Hacker
1632 Posts |
Posted - 2004-08-16 : 09:23:48
|
| Another option might be to package up the data as an xml string and use OPENXML. |
|
|
|
|
|
|
|
|
VarChar 8000 Limit and Arrays