| Author |
Topic |
|
Utpal
Posting Yak Master
179 Posts |
 Posted - 2002-03-03 : 00:24:28
|
| Hi everybody, Can arrays be passed as parameters to stored procedures ? Or is there any other way to emulate that ? |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-03 : 00:41:33
|
| You could pass a delimited string and pick out the parts you wantYou could also use dynamic SQL. Do a search on this site for dyanmic SQL.Michael |
 |
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-03 : 00:41:39
|
| You could pass a delimited string and pick out the parts you wantYou could also use dynamic SQL. Do a search on this site for dyanmic SQL.Michael |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-03-03 : 00:41:43
|
| You could pass a delimited string and pick out the parts you wantYou could also use dynamic SQL. Do a search on this site for dyanmic SQL.Michael |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-04 : 05:48:13
|
| Thanks a lot ! |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-04 : 07:18:59
|
| Graz's technique would not allow to pass an unlimited number of elements to the stored procedure through a single string, because the length of the parameter has to be specified e.g. Array_Name VarChar(1000). If a huge parameter length is specified e.g. VarChar(1000000000000), would that consume a lot of memory even if the length of the string passed is less ? |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-04 : 07:24:26
|
| The maximum length of a string that can be passed is 8000 characters. If you need to pass more use multiple varchar(8000) parameters.I don't know how much data you're trying to pass to the stored procedure, but it can accept up to 1024 parameters of 8000 characters each, that's 8MB of data. While this is a relatively minor amount of memory for SQL Server to handle, it's an excessive amount to pass to a stored procedure. If you feel you need to pass more than 200K of data you should reconsider your design.Edited by - robvolk on 03/04/2002 07:25:28 |
|
|
|
davidpardoe
Constraint Violating Yak Guru
324 Posts |
Posted - 2002-03-04 : 08:03:40
|
Way to go MichaelP on the old get the posts up high thing  ============The Dabbler! |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-05 : 05:12:45
|
| Thanks a lot Robvolk !Using multiple parameters would be difficult. I need to pass arrays for each of my columns in a Purchase Order detail block. I'll have to think of how to organize and use the parameters in the stored procedure. I'll give it a try. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-05 : 14:57:01
|
| There's an idea here you might want to try:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=12538It uses multiple parameters of fixed-length values and splits them out for INSERT. Each column would be passed as a parameter (10 columns = 10 parameters). I doubt you have 1024 columns to insert (!) so this should work fine. You need to modify this though if you are going to insert Nulls into the table.Unless you are INSERTing several hundred rows from the detail area, or have some incredibly complicated process, there's nothing wrong with calling the SP multiple times to insert the data. I wrote an ASP page a while back that would call the SP for each row inserted; it tested fine for 500 rows, and was only a little slow for 1,000 row inserts. This was far more than would ever actually be done in one order. Not saying it's the most efficient way, but unless you're seeing performance problems now there's no point making the process more complicated. |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-06 : 04:42:49
|
| The sequence table concept is a genius of an idea ! It took me quite some time to grasp it. I could use that concept for not only implementing the multiple insert SP but also for other things like a SP to execute a multiple keyword search on a table and maybe a lot others.However, I don't want to call the SP multiple times if the number of inserts exceed 8000/Len(value to be inserted), because I want to execute the inserts as a single transaction within a BEGIN TRAN and COMMIT TRAN. This is because in my case I am saving a new Purchase Order. I want to save the Purchase Order header and item details as a single transaction, so that either everything in the Purchase Order is saved, or if something goes wrong, nothing is saved (ROLLBACK occurs). Am I thinking right ?Edited by - Utpal on 03/06/2002 04:47:02 |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-03-06 : 07:03:37
|
Yes, that's correct, and keeping it in a transaction is a good idea and makes sense. As long as you can make it work without rewriting everything or otherwise killing yourself!  |
|
|
|
Utpal
Posting Yak Master
179 Posts |
Posted - 2002-03-07 : 04:50:21
|
| Thanks ! |
|
|
|
|
Array parameters ?